SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query


Query

Author
Message
ramana3327
ramana3327
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2095 Visits: 2083
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,
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12401 Visits: 8548
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
Ed Wagner
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16609 Visits: 10063
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
ramana3327
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2095 Visits: 2083
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
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12401 Visits: 8548
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
ramana3327
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2095 Visits: 2083
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
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85923 Visits: 41091
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search