Query

Question

Query

ramana3327

SSCoach

Points: 19700
More actions
June 10, 2014 at 5:53 pm

#308071

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,

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

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

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 on googles mail service

Ed Wagner SSC Guru Points: 287024 More actions · Answer 2

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

ramana3327 SSCoach Points: 19700 More actions · Answer 3

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

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 on googles mail service

ramana3327 SSCoach Points: 19700 More actions · Answer 5

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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 6

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.

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)