Queries on Queries for Performance

Question

Queries on Queries for Performance

sql_butterfly

Ten Centuries

Points: 1264
More actions
January 22, 2011 at 8:27 am

#230681

I have few basic questions (Consider TableA as a very big table with over 3000000 rows):
1. Is this the right way to check some condition:
IF EXISTS(SELECT * FROM TableA WHERE ColA = 'blahblahblah')
BEGIN
--------------
--------------
END
2. Is this correct:
SELECT * FROM TableA
WHERE ColA <> 64
3. Is this corect:
SELECT * FROM TableA
WHERE ((ColA = 5) or (ColA = 8))
4. Is this correct:
SELECT 'ColB' = CASE WHEN (A.ColA IN(select * from TableB) and A.Colc = '') then 'BlahBlahBlah'
ELSE 'Lolz' END
FROM TableA A

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 1

sql_butterfly (1/22/2011)
I have few basic questions (Consider TableA as a very big table with over 3000000 rows):
1. Is this the right way to check some condition:
IF EXISTS(SELECT * FROM TableA WHERE ColA = 'blahblahblah')
BEGIN
--------------
--------------
END
2. Is this correct:
SELECT * FROM TableA
WHERE ColA <> 64
3. Is this corect:
SELECT * FROM TableA
WHERE ((ColA = 5) or (ColA = 8))
4. Is this correct:
SELECT 'ColB' = CASE WHEN (A.ColA IN(select * from TableB) and A.Colc = '') then 'BlahBlahBlah'
ELSE 'Lolz' END
FROM TableA A

Gosh... do what's right and setup some tests and run them on your own. How are you expecting to really learn anything if you don't do your own homework or interview questions?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sql_butterfly Ten Centuries Points: 1264 More actions · Answer 2

Hello Sir, its not like I didn't give it a try. I read a lot and tried lotz of options and tested them as well. However whatever I have tested not giving me satisfying results. Suppose if a query is taking say 10 seconds originally, after lots of trials and testings based on knowledge gathering its taking same time. I have also checked with different combinations of indexes on tables as well with the help of DTA as well as certain DMVs like sys.dm_db_missing_index_details, sys.dm_db_missing_index_columns, sys.dm_db_missing_index_groups etc. Also kept in mind the index health in terms of defragmentation. Once I was done and not yet satisfied, then only I came here.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

sql_butterfly (1/22/2011)
2. Is this correct:
SELECT * FROM TableA
WHERE ColA <> 64

Think about it, if you want all rows except where ColA = 64, how else would you do it?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

The answer is simple, it depends. Each query is an answer to a different question. Without knowing the question, we can't tell you if the query is correct or not.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 5

sql_butterfly (1/23/2011)
Hello Sir, its not like I didn't give it a try. I read a lot and tried lotz of options and tested them as well. However whatever I have tested not giving me satisfying results. Suppose if a query is taking say 10 seconds originally, after lots of trials and testings based on knowledge gathering its taking same time. I have also checked with different combinations of indexes on tables as well with the help of DTA as well as certain DMVs like sys.dm_db_missing_index_details, sys.dm_db_missing_index_columns, sys.dm_db_missing_index_groups etc. Also kept in mind the index health in terms of defragmentation. Once I was done and not yet satisfied, then only I came here.

I'm a little skeptical of your answer because the code from one of the questions won't even pass a simple syntax check. :Whistling:

So how did you answer on the 4 questions and why?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

sql_butterfly (1/23/2011)
Hello Sir, its not like I didn't give it a try. I read a lot and tried lotz of options and tested them as well. However whatever I have tested not giving me satisfying results. Suppose if a query is taking say 10 seconds originally, after lots of trials and testings based on knowledge gathering its taking same time. I have also checked with different combinations of indexes on tables as well with the help of DTA as well as certain DMVs like sys.dm_db_missing_index_details, sys.dm_db_missing_index_columns, sys.dm_db_missing_index_groups etc. Also kept in mind the index health in terms of defragmentation. Once I was done and not yet satisfied, then only I came here.

Seems that what you really want is performance tuning. This is a HUGE topic, and definitely not something you can learn from a few forum threads. Most importantly is that you could tune each of those queries to be blazing fast but totally hose other queries. There is no simple answer.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service