Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Let's talk about query performance

Posted on 2014-12-09
3
Medium Priority
?
142 Views
Last Modified: 2014-12-24
I have a question about query performance: retrieving values in the query with a pivot or left join versus retrieving them on the row of an ItemDataBound in the grid:

To get my grid values, I can do a "select * from UserProfile where ..."

I have a several fields in a table with responses that I would use a PIVOT or left join to create other columns:
like:
UserName   Age    City    State   Zip Haircolor
These are all in a responses table (these are examples, there are numerous responses); but none of these are in the UserProfile table. Each has its own row in the responses table.

As I said, I could use a pivot to create the query, or I could instead, when binding the grid, on the itembound go look up the data for that one row's user and display it in the grid at that time.

I'm thinking this will be better performance, because my grid will display maybe 100-500 results to a page, whereas, if I put this in a pivot, it will do it for every single record in the select clause, which could be maybe 5000 or 50,000 or whatever it gets to.

Also, in doing this, I might want to add to my main query some filtering, like:
select * from userprofile where exists (select 1 from ResponseTable where userprofile.userprofileid = ResponseTable .userprofileId and ResponseTypeId = 32)

So I *AM* going to be adding some subqueries to the actual query for filtering, but I don't need to really do this for some of the displayed fields such as, in my example, city, hair color (they won't be in the search filter). So entirely leaving out all the desired display results from the query and only using the exists clause on the search filters, plus adding the display data to the grid on load by doing queries at that time, seems to me would be the better performance.

I will probably look up about 6-10 values for each row that is displayed to the screen on the ItemDataBound; as opposed to putting PIVOT subqueries or even left joins on each of these 6-10 values:

For example, here's an actual query with only 2 of these values using a left join:
select username,
qaCity.textanswer as MyCity,
lvRace.ListValueName as MyRace
 from userprofile 
 left join qaset on qaset.userprofileid = userprofile.userprofileid
left join qafield qaCity 
  on qaset.qasetid = qaCity.qasetid
  and qaCity.questionvalueid = 34

  left join qaList qaRace 
  on qaset.qasetid = qaRace.qasetid
  and qaRace.questionvalueid = 32
    left join ListValue lvRace 
  on lvRace.ListValueId = qaRace.ListValueId
  where username <> ''

Open in new window


What do you think? What's the best performance method? 100 rows in the grid, or all 5000 rows in the query?

thanks.
0
Comment
Question by:Starr Duskk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 668 total points
ID: 40491106
I believe the latter but nothing is shure. So much depends, and no one knows your details.

This is one of the type of questions where I recommend: Why not just check out both methods? It cannot take many hours, and then you have firm knowledge.

/gustav
0
 
LVL 35

Assisted Solution

by:ste5an
ste5an earned 668 total points
ID: 40491184
Please rephrase your question. What performance do you want to look at?

When it's about query performance, than it depends on your SQL Server instance. Especially cold buffers vs. hot buffers and proper indexing. Here it makes often no difference whether you generate the pivot here or return only the partial datasets. Often means: You need to test and measure it. There is no rule of thumb.

When it's about the overall performance, then calculating the pivot or an appropriate single resultset makes sense. Cause this reduces the round-trips from your application to the SQL Server. btw, your describtion does not sound like a pivot (changing rows to columns). It sounds like simply adding columns to the result set from different tables.

I don't understand your different cardinality estimates.

Can you give us a concise and complete example. Especially how does your data model looks like?
SELECT  username ,
        qaCity.textanswer AS MyCity ,
        lvRace.ListValueName AS MyRace
FROM    userprofile
        LEFT JOIN qaset ON qaset.userprofileid = userprofile.userprofileid
        LEFT JOIN qafield qaCity ON qaset.qasetid = qaCity.qasetid AND qaCity.questionvalueid = 34
        LEFT JOIN qaList qaRace ON qaset.qasetid = qaRace.qasetid AND qaRace.questionvalueid = 32
        LEFT JOIN ListValue lvRace ON lvRace.ListValueId = qaRace.ListValueId
WHERE   username <> ''

Open in new window

0
 
LVL 96

Assisted Solution

by:Bob Learned
Bob Learned earned 664 total points
ID: 40497311
Query optimization is part art and part science, and you can't even begin to optimize until we know what you are working with.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question