Tuning Select Query - 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)

Tuning Select Query

Author

Topic

chitts
Starting Member

7 Posts

Posted - 2002-03-08 : 18:41:17

Dear Developers,

We are having a table which has almost all the columns indexed. The
table contains 5,00,000 records.

I have this query which takes 5 secs to complete

SET ROWCOUNT 5
select field1,field2 from table1 where (field3 ='test' or field4='y')
and upper(field5 ) = 'C1' and upper(field6) <>'C2'
and field1 in (select field1 from table1 where field7 in ('C3','C4'))
order by field8 desc
SET ROWCOUNT 0

field 1 is varchar
field2 is varchar
field3 is varchar
field4 is char(1)
field5 is varchar
field6 is varchar
field7 is varchar
field8 is date

C1,C2,C3,C4 are constants

Is there any way to increase the performance ? The most concerning
matter is the records keep on increasing.. Can you Pls show me the way to
optimize this query..

Thanks in advance. Eagerly awaiting for your earliest replies.

kaus
Posting Yak Master

179 Posts

Posted - 2002-03-08 : 19:10:29

do you need the subquery since its only one table ??
select field1,field2 from table1 where (field3 ='test' or field4='y')
and (field7 = 'c3'or field7 = 'c4')
and upper(field5 ) = 'C1' and upper(field6) <>'C2'
order by field8 desc

I think that the in () clause also slows things down

Pete

Lou
Yak Posting Veteran

59 Posts

Posted - 2002-03-08 : 19:34:37

This site has some good tips.

http://www.sql-server-performance.com/transact_sql.asp

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-03-09 : 06:37:29

quote:

do you need the subquery since its only one table ??

You do.

quote:

select field1,field2 from table1 where (field3 ='test' or field4='y')
and (field7 = 'c3'or field7 = 'c4')
and upper(field5 ) = 'C1' and upper(field6) <>'C2'
order by field8 desc

This is incorrect. It would only match the first of the following:


Field 1  Field 7
-------  -------
      1       C3
      1       C5

Edited by - Arnold Fribble on 03/09/2002 06:38:12

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-03-09 : 10:18:25

For starters why don't you run this script and post the result:

set showplan_text on
go
SET ROWCOUNT 5
select field1,field2 from table1 where (field3 ='test' or field4='y')
and upper(field5 ) = 'C1' and upper(field6) <>'C2'
and field1 in (select field1 from table1 where field7 in ('C3','C4'))
order by field8 desc
SET ROWCOUNT 0
go
set showplan_text off
go

I've found in testing on a case sensitive system that using UPPER(fieldname) is verrry slow since it will usually won't use an index. You might consider:

(field5 = 'c1' or field5 = 'C1')

And if you system isn't case sensitive then don't worry about the UPPER.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

chitts
Starting Member

7 Posts

Posted - 2002-03-11 : 15:02:51

Dear Graz,
I have posted the showplan. The most interesting thing is that..
when i remove the upper() it takes a longer time to execute.

 |--Sort(ORDER BY:([table1].[field8] DESC))
       |--Nested Loops(Left Semi Join)
            |--Filter(WHERE:((([table1].[field3]='test' OR [table1].[field4]='y') AND upper([table1].[field6])<>'C2') AND upper([table1].[field5])='C1'))
            |    |--Table Scan(OBJECT:([db].[dbo].[table1]))
            |--Filter(WHERE:([table1].[field7]='C3' OR [table1].[field7]='C4'))
                 |--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([db].[dbo].[table1]))
                      |--Index Seek(OBJECT:([db].[dbo].[table1].[table_P]), SEEK:([table1].[field1]=[table].[field1]) ORDERED)

hoping to get an optimized query

-Chitts

(Edited by graz to add CODE tags to I could read it)

Lou
Yak Posting Veteran

59 Posts

Posted - 2002-03-12 : 00:43:01

Try the following. If you keep getting a table scan, you might try a index hint. HTH.

select top 5 a.field1,field2
from
(
select field1
from table1
where field7 in ('C3','C4')
) as a
JOIN
table1 as b
ON a.field1 = b.field1
where
upper(field5 ) = 'C1'
and (field3 ='test' or field4='y')
and upper(field6) <>'C2'
order by field8 desc

Edited by - lou on 03/12/2002 00:44:54

chitts
Starting Member

7 Posts

Posted - 2002-03-12 : 11:54:41

lou,
I have modified the query as you said.. but still its takes the same time..
I have attached the showplan there is no table search...
I have removed the upper() also..

|--Top(5)
|--Sort(ORDER BY:([b].[field8] DESC))
|--Filter(WHERE:([table1].[field7]='C3' OR [table1].[field7]='C4'))
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([db].[dbo].[table]))
|--Nested Loops(Inner Join)
|--Filter(WHERE:([b].[field4]='y' OR [b].[field3]='test'))
| |--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([db].[dbo].[table1] AS [b]) WITH PREFETCH)
| |--Hash Match(Inner Join, HASH:([Bmk1002])=([Bmk1002]), RESIDUAL:([Bmk1002]=[Bmk1002]))
| |--Index Scan(OBJECT:([db].[dbo].[table1].[table1_17] AS [b]), WHERE:([b].[field5]='C1'))
| |--Index Seek(OBJECT:([db].[dbo].[table].[table_11] AS [b]), SEEK:([b].[field6] < 'C2' OR [b].[field6] > 'C2') ORDERED)
|--Index Seek(OBJECT:([db].[dbo].[table].[table_P]), SEEK:([probsummary].[field1]=[b].[field1]) ORDERED)

The most interesting thing is that its not using multiple tables.. using singe table with all the searchable columns in the where clause are indexed.. still its taking a long time..

Hope to get an optimized query..

Thanks
Chitts

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-03-12 : 12:47:17

maybe i'm coming late to the party on this one, but would not the problem be to do with the "order by field8 desc" combined with a potential 5,000,000 row resultset.

the "set rowcount 5" will only take effect after the sort. no matter what indices you have, unless the machine you have is 'powerful'....i'd say this operation alone should take some time.

or...can the "field8" be indexed backwards....ie in descending order?
would this reduce the sort?

Lou
Yak Posting Veteran

59 Posts

Posted - 2002-03-12 : 13:14:45

Well, at least the query is no longer doing table scans. Can you run the query in query analyzer with 'show execution plan (ctrl+k)' in effect? The graphical output will tell us exactly what clause is hogging the processor, in percent. (Look for something that says like 75% or 90%).

Edited by - lou on 03/12/2002 13:16:39

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-03-12 : 15:50:05

Lou, that query may return different results: it will get repeats for each multiply-occurring field1 value. The reason it's not scanning the table anymore is that the UPPER was removed from the field6 comparison.

Chitts, any chance you could give us the results (or ballpark figures) of the following:


SELECT COUNT(*)
FROM table1
WHERE field6 <> 'C2'


SELECT COUNT(*)
FROM table1
WHERE (field3 = 'test' OR field4 = 'y')
  AND field5 = 'C1' AND field6 <> 'C2'


SELECT COUNT(DISTINCT field1)
FROM table1


SELECT COUNT(DISTINCT field1)
FROM table1
WHERE field7 IN ('C3', 'C4)


SELECT COUNT(*)
FROM table 1
WHERE (field3 = 'test' OR field4 = 'y')
  AND field5 = 'C1' AND field6 <> 'C2'
  AND field1 IN (
    SELECT field1
    FROM table1
    WHERE field7 IN ('C3', 'C4'))

Edited by - Arnold Fribble on 03/12/2002 15:52:00

chitts
Starting Member

7 Posts

Posted - 2002-03-13 : 13:58:55

Thanks guys,
Atlast we were able to write a query which takes only 1 sec..

set rowcount 5
select field1, field2 from table where field6 <> 'c2' and
field1 in (select field1 from table where (field3 ='test' or field4 ='y') and field5 = 'C1' and field7 in ('C3','c4')) order by field8 desc
set rowcount 0

|--Sort(ORDER BY:([table].[field8] DESC))
|--Filter(WHERE:([table].[field6]<>'C2'))
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([db].[dbo].[table]) WITH PREFETCH)
|--Nested Loops(Inner Join)
|--Filter(WHERE:([table].[field5]='test' OR [table].[field4]='y'))
| |--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([db].[dbo].[table]) WITH PREFETCH)
| |--Parallelism(Gather Streams)
| |--Hash Match(Inner Join, HASH:([Bmk1002])=([Bmk1002]), RESIDUAL:([Bmk1002]=[Bmk1002]))
| |--Parallelism(Repartition Streams, PARTITION COLUMNS:([Bmk1002]))
| | |--Index Scan(OBJECT:([db].[dbo].[table].[table_17]), WHERE:([table].[field5]='C1'))
| |--Parallelism(Repartition Streams, PARTITION COLUMNS:([Bmk1002]))
| |--Index Seek(OBJECT:([db].[dbo].[table].[table_field7]), SEEK:([table].[field7] BETWEEN C3' AND C3' OR [table].[field7] BETWEEN 'C3' AND 'C4') ORDERED |--Index Seek(OBJECT:([db].[dbo].[table].[table_P]), SEEK:([table].[field1]=[table].[field1]) ORDERED)

Thanks a lot guys..

Chitts

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources