Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query


Query

Author
Message
ramana3327
ramana3327
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1256 Visits: 2064
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
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6002 Visits: 8314
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
Ed Wagner
Ed Wagner
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10324 Visits: 9600
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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1256 Visits: 2064
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
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6002 Visits: 8314
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
ramana3327
ramana3327
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1256 Visits: 2064
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-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45271 Visits: 39934
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

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