How to optimze the search - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to optimze the search

Author

Topic

sheetalar
Starting Member

17 Posts

Posted - 2004-10-12 : 02:27:46

Hi

I am havig two tables one is having around 2000 records and another is having arnd 10,000 records.

I select one record from my first table and find all the records which are in some range from my second table. Similarly for for the second record, third record and so on...

Now my problem is for complete process it takes around 20 - 40 sec... I want to reduce it below 5 sec...

the sql I am using for this is:

SELECT X_COORD, Y_COORD FROM LANDMARK WHERE SQUARE(X_COORD - 77) + SQUARE(Y_COORD - 26) <= (SELECT MIN(SQUARE(X_COORD - 77) + SQUARE(Y_COORD - 26)) AS DIST FROM LANDMARK WHERE X_COORD BETWEEN 77 - 0.2 AND 77 + 0.2 AND Y_COORD BETWEEN 26 - 0.2 AND 26 + 0.2)

How can I reduce the time below 5 sec?

Regards
Sheetal Arya

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2004-10-12 : 03:34:41

You have to look Query execution plan !.. Check proper index are created in the table!

- Sekar

sheetalar
Starting Member

17 Posts

Posted - 2004-10-12 : 03:42:42

Yes it is properly indexed... even then it is taking too much of time... can I run multiple instances of the same query say first 100 with one instance and next 100 with second instance and so on... or something of that sort...

thanx
sheetal

quote:
Originally posted by samsekar

You have to look Query execution plan !.. Check proper index are created in the table!

- Sekar

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-12 : 03:46:32

will it help if you would set parentheses for arithmetic groupings?

also since your subquery will not change it's value during the transaction, can you just save this in a variable and use that variable as you criteria? i believe this will reduce the nth computation to 1.

--------------------
keeping it simple...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-12 : 04:14:09

would this work for you?


SELECT 	t1.X_COORD, t2.Y_COORD 
FROM 	LANDMARK t1
	inner join (SELECT MIN(SQUARE(X_COORD - 77) + SQUARE(Y_COORD - 26)) AS DIST 
			FROM 	LANDMARK 
			WHERE 	X_COORD BETWEEN 77 - 0.2 AND 
				77 + 0.2 AND 
				Y_COORD BETWEEN 26 - 0.2 AND 
				26 + 0.2)) t2 on
	(SQUARE(t1.X_COORD - 77) + SQUARE(t1.Y_COORD - 26)) <= t2.DIST

Go with the flow & have fun! Else fight the flow

sheetalar
Starting Member

17 Posts

Posted - 2004-10-12 : 04:44:59

Thanks,

but it is still not working for me... it is taking even more time what you have suggested...

Regards
Sheetal

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-12 : 04:49:15

more time????

look at the execution plans and tell us where the bottlenecks are.

Go with the flow & have fun! Else fight the flow

sheetalar
Starting Member

17 Posts

Posted - 2004-10-12 : 05:05:44

This object is taking the maximum time...

OBJECT: ([DB].[DBO].[LANDMARK].[XY] AS [T1])

Cost: 72%

Regards
Sheetal

sheetalar
Starting Member

17 Posts

Posted - 2004-10-12 : 05:11:15

this nested loop is also taking too much of time...

WHERE:(SQUARE(CONVERT([T1].[X_COORD] - 77) + SQUARE(CONVERT([T1].[Y_COORD] - 26) <= [EXP1002])

Cost: 23%

Regards
Sheetal

Kristen
Test

22859 Posts

Posted - 2004-10-12 : 05:21:46

I find it easier to bottom performance issues by running the query on a specific example, and surrounding it with:

SET STATISTICS IO ON; SET STATISTICS TIME ON

... put query here

SET STATISTICS IO OFF; SET STATISTICS TIME OFF

and then working to get the Scans and Logical reads as low as possible.

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-12 : 05:21:50

and in my query?? what's slow there?

have you tried jen's solution?


declare @xySquare int
set @xySquare = (SELECT MIN(SQUARE(X_COORD - 77) + SQUARE(Y_COORD - 26)) AS DIST FROM LANDMARK WHERE X_COORD BETWEEN 77 - 0.2 
                 AND 77 + 0.2 AND Y_COORD BETWEEN 26 - 0.2 AND 26 + 0.2)

SELECT X_COORD, Y_COORD FROM LANDMARK WHERE SQUARE(X_COORD - 77) + SQUARE(Y_COORD - 26) <= @xySquare

Go with the flow & have fun! Else fight the flow

sheetalar
Starting Member

17 Posts

Posted - 2004-10-12 : 05:28:42

Yes I have tried this... this takes over 5 min...

sheetalar
Starting Member

17 Posts

Posted - 2004-10-12 : 05:41:14

STATISTICS:

SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 44 ms.

(1 row(s) affected)

Table 'landmark'. Scan count 2, logical reads 131, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 76 ms.

SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 76 ms.

SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 77 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

Query:
SELECT t1.X_COORD, t1.Y_COORD
FROM LANDMARK t1
inner join (SELECT MIN(SQUARE(X_COORD - 77) + SQUARE(Y_COORD - 26)) AS DIST
FROM LANDMARK
WHERE X_COORD BETWEEN 77 - 0.2 AND
77 + 0.2 AND
Y_COORD BETWEEN 26 - 0.2 AND
26 + 0.2) t2 on
(SQUARE(t1.X_COORD - 77) + SQUARE(t1.Y_COORD - 26)) <= t2.DIST

Thanks & Regards
Sheetal

sheetalar
Starting Member

17 Posts

Posted - 2004-10-12 : 05:46:59

These statistics is for the one record only... i have to loop through 2000 records like this...

STATISTICS:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'landmark'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 1 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 1 ms.
Table 'landmark'. Scan count 1, logical reads 124, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 25 ms.

(0 row(s) affected)

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 25 ms.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 25 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

Query:

declare @xySquare int
set @xySquare = (SELECT MIN(SQUARE(X_COORD - 77) + SQUARE(Y_COORD - 26)) AS DIST FROM LANDMARK WHERE X_COORD BETWEEN 77 - 0.2
AND 77 + 0.2 AND Y_COORD BETWEEN 26 - 0.2 AND 26 + 0.2)

SELECT X_COORD, Y_COORD FROM LANDMARK WHERE SQUARE(X_COORD - 77) + SQUARE(Y_COORD - 26) <= @xySquare

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-12 : 05:50:22

could you post the statistics for the other two methods (jen's and yours)
all this is very illogical to me..

Go with the flow & have fun! Else fight the flow

sheetalar
Starting Member

17 Posts

Posted - 2004-10-12 : 05:54:36

STATISTICS:

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)

Table 'landmark'. Scan count 2, logical reads 131, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 27 ms, elapsed time = 27 ms.

SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 28 ms.

SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 28 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

Query:

SELECT X_COORD, Y_COORD FROM LANDMARK WHERE SQUARE(X_COORD - 77) + SQUARE(Y_COORD - 26) <= (SELECT MIN(SQUARE(X_COORD - 77) + SQUARE(Y_COORD - 26)) AS DIST FROM LANDMARK WHERE X_COORD BETWEEN 77 - 0.2 AND 77 + 0.2 AND Y_COORD BETWEEN 26 - 0.2 AND 26 + 0.2)

Thanks & Regards
Sheetal

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-12 : 05:56:48

what if you change
MIN(SQUARE(X_COORD - 77) + SQUARE(Y_COORD - 26))
to
SQUARE(min(X_COORD) - 77) + SQUARE(min(Y_COORD) - 26)

Go with the flow & have fun! Else fight the flow

sheetalar
Starting Member

17 Posts

Posted - 2004-10-12 : 06:00:53

No this wont work...

actually by this I am trying to calculate min distance... this will then give me wrong results...

can i create 4 - 5 threads and pass it to sql for execution...

Regards
Sheetal

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-12 : 06:04:43

aha... ok

what do you mean by:
can i create 4 - 5 threads and pass it to sql for execution...

Go with the flow & have fun! Else fight the flow

sheetalar
Starting Member

17 Posts

Posted - 2004-10-12 : 06:30:02

Ya I mean, now what I am doing is taking one record at a time n then quering it... and I can not query it further until unless my previous sql statment is executed... if some how I can keep on passing my sql queries irrespective of my previous sql statement is executed or not with different instance, will that not speed up the things... as right now it is arnd 30 sec... to get it down to 5 sec using some query will be difficult... I feel I will have to do something of this sort only to get it down to below 5 sec...

Regards
Sheetal

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-10-12 : 07:05:32

Try looking at some of the stuff about halfway down page 1 of this thread:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=16859

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources