Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Expand / Collapse
Author
Message
Posted Tuesday, June 10, 2014 5:53 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 12:42 AM
Points: 675, Visits: 913
Hi All,

I do have table Ex: customer. I do have some select statements based in where clause studentid.

I am retrieving

student name, age , marital status, contact no from student where student id=1
student name, age , marital status, contact no from student where student id=2
student name, age , marital status, contact no from student where student id=3

I tried in the following way

select student name, age , marital status, contact no from student where student id in (1,2,3)
and I tried

student name, age , marital status, contact no from student where student id=1 or student id=2 or student id=3

but the total no of records are varying. Is there any way to optimize those queries

Thanks,
Post #1579448
Posted Tuesday, June 10, 2014 6:26 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:17 PM
Points: 4,474, Visits: 6,407
ramana3327 (6/10/2014)
Hi All,

I do have table Ex: customer. I do have some select statements based in where clause studentid.

I am retrieving

student name, age , marital status, contact no from student where student id=1
student name, age , marital status, contact no from student where student id=2
student name, age , marital status, contact no from student where student id=3

I tried in the following way

select student name, age , marital status, contact no from student where student id in (1,2,3)
and I tried

student name, age , marital status, contact no from student where student id=1 or student id=2 or student id=3

but the total no of records are varying. Is there any way to optimize those queries

Thanks,


1) I am having a hard time seeing that those queries returned something other than the same 3 rows (assuming you have records with IDs of 1, 2 and 3).

2) What makes you think those queries are not optimized already? What metric did you use to say they were not returned as fast or as efficient as they could be?


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1579451
Posted Tuesday, June 10, 2014 7:06 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 11:36 AM
Points: 4,625, Visits: 4,085
TheSQLGuru (6/10/2014)
ramana3327 (6/10/2014)
Hi All,

I do have table Ex: customer. I do have some select statements based in where clause studentid.

I am retrieving

student name, age , marital status, contact no from student where student id=1
student name, age , marital status, contact no from student where student id=2
student name, age , marital status, contact no from student where student id=3

I tried in the following way

select student name, age , marital status, contact no from student where student id in (1,2,3)
and I tried

student name, age , marital status, contact no from student where student id=1 or student id=2 or student id=3

but the total no of records are varying. Is there any way to optimize those queries

Thanks,


1) I am having a hard time seeing that those queries returned something other than the same 3 rows (assuming you have records with IDs of 1, 2 and 3).

2) What makes you think those queries are not optimized already? What metric did you use to say they were not returned as fast or as efficient as they could be?

I'm with Kevin on this one. There must be something else to it that I'm not seeing if those queries return different data. Could you please post the DDL for the table and the SQL queries you actually ran?



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1579454
Posted Wednesday, June 11, 2014 10:27 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 12:42 AM
Points: 675, Visits: 913
Thanks for the replies.

I heard from some one instead of reading the table 3 times it is better to read one time. So I thought of using in single query
Post #1579725
Posted Thursday, June 12, 2014 10:00 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:17 PM
Points: 4,474, Visits: 6,407
Removing extra hits on a table is a HUGELY important thing in many tuning exercises, and it is good that you did that here. But you stated you got different results which outside of a SQL Server bug or someone changing the data under you just isn't possible.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1580142
Posted Friday, June 13, 2014 11:05 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 12:42 AM
Points: 675, Visits: 913
I saw only records from one query result set and I thought it is giving all together 3 queries result set. Sorry for my mistake. It is giving same no of records but I do have one question when I run the query by using in where id in (1,2,3) is taking less time than compare to where id=1 or where id=2 or where id=3
Post #1580653
Posted Friday, June 13, 2014 5:01 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:05 PM
Points: 35,832, Visits: 32,505
ramana3327 (6/13/2014)
I saw only records from one query result set and I thought it is giving all together 3 queries result set. Sorry for my mistake. It is giving same no of records but I do have one question when I run the query by using in where id in (1,2,3) is taking less time than compare to where id=1 or where id=2 or where id=3


Test it and find out. Report your findings when you've completed your test(s).


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1580760
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse