Perfomance

Hi All,
I have a little bit of code that looks something like this
SELECT
code,
[description],
clientDescription,
band,
AVG(rental) AS value,
COUNT(tableTemp.surveyID) AS [count],
CASE clientID
WHEN @.clientID THEN 1
ELSE 0
END AS color
FROM
(
SELECT tblSurvey.surveyID,
tblSurvey.clientID AS clientID,
client_alias AS code,
CONVERT(varchar(25), client_alias) AS [description],
client_desc AS clientDescription,
#StartOfPeriod.band,
rental
FROM tblSurvey
INNER JOIN tblClient
ON tblClient.clientID = tblSurvey.clientID
INNER JOIN #surveys
ON #surveys.surveyID = tblSurvey.surveyID
INNER JOIN #StartOfPeriod
ON #StartOfPeriod.editionID=tblSurvey.editionID
WHERE (mileage = @.mileageint OR @.mileageint = 0)
AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
AND tblSurvey.editionID >= @.startEdition
AND tblSurvey.editionID < @.limitEdition
AND client_alias IS NOT NULL
AND rental IS NOT NULL
) AS tableTemp
GROUP BY code,
[description],
clientDescription,
band,
tableTemp.clientID
UNION ALL
SELECT
'99' AS code,
'IMV' AS [description],
'IMV' AS clientDescription,
band,
AVG(rental) AS value,
COUNT(averageTemp.surveyID) AS [count],
0 AS color
FROM (SELECT tblSurvey.surveyID,
#StartOfPeriod.band,
rental
FROM tblSurvey
INNER JOIN tblClient
ON tblClient.clientID = tblSurvey.clientID
INNER JOIN #surveys
ON #surveys.surveyID = tblSurvey.surveyID
INNER JOIN #StartOfPeriod
ON #StartOfPeriod.editionID=tblSurvey.editionID
WHERE (mileage = @.mileageint OR @.mileageint = 0)
AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
AND tblSurvey.editionID >= @.startEdition
AND tblSurvey.editionID < @.limitEdition
AND client_alias IS NOT NULL
AND rental IS NOT NULL
) AS averageTemp
GROUP BY band
I have tried everything I can think of to make this run quicker, even though
it's not really slow, just want to get the best performance possible. There
are no way's around the temp tables that I need to use so I am just wonderin
g
if there is a better way that I can write the select statement'
Any help would be great.
Thanks PhilHave you tried something like this already? Your using select statements
with selects statements in an Union. I can imagine that your solutions isn't
the fasted way to get the information.
SELECT code
, [description]
, clientDescription
, COUNT(tableTemp.surveyID) AS [count]
, tblSurvey.clientID AS clientID
, client_alias AS code
, CONVERT(varchar(25), client_alias) AS [description], client_desc AS
clientDescription
, #StartOfPeriod.band
, rental
, CASE clientID WHEN @.clientID THEN 1 ELSE 0 END AS color
FROM tblSurvey
<-- Rest of Query -->
GROUP BY code, [description], clientDescription, band, tableTemp.clientID
UNION ALL
SELECT '99' AS code
,'IMV' AS [description]
,'IMV' AS clientDescription
,COUNT(averageTemp.surveyID) AS [count]
, #StartOfPeriod.band as [band]
,AVG(rental) AS value
,0 AS color
fROM tblSurvey
INNER JOIN tblClient
AND client_alias IS NOT NULL
AND rental IS NOT NULL
<-- Rest of Query -->
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:CE9CEEE5-9D1E-4735-9A1A-72B01C5105FA@.microsoft.com...
> Hi All,
> I have a little bit of code that looks something like this
> SELECT
> code,
> [description],
> clientDescription,
> band,
> AVG(rental) AS value,
> COUNT(tableTemp.surveyID) AS [count],
> CASE clientID
> WHEN @.clientID THEN 1
> ELSE 0
> END AS color
> FROM
> (
> SELECT tblSurvey.surveyID,
> tblSurvey.clientID AS clientID,
> client_alias AS code,
> CONVERT(varchar(25), client_alias) AS [description],
> client_desc AS clientDescription,
> #StartOfPeriod.band,
> rental
> FROM tblSurvey
> INNER JOIN tblClient
> ON tblClient.clientID = tblSurvey.clientID
> INNER JOIN #surveys
> ON #surveys.surveyID = tblSurvey.surveyID
> INNER JOIN #StartOfPeriod
> ON #StartOfPeriod.editionID=tblSurvey.editionID
> WHERE (mileage = @.mileageint OR @.mileageint = 0)
> AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
> AND tblSurvey.editionID >= @.startEdition
> AND tblSurvey.editionID < @.limitEdition
> AND client_alias IS NOT NULL
> AND rental IS NOT NULL
> ) AS tableTemp
> GROUP BY code,
> [description],
> clientDescription,
> band,
> tableTemp.clientID
> UNION ALL
> SELECT
> '99' AS code,
> 'IMV' AS [description],
> 'IMV' AS clientDescription,
> band,
> AVG(rental) AS value,
> COUNT(averageTemp.surveyID) AS [count],
> 0 AS color
> FROM (SELECT tblSurvey.surveyID,
> #StartOfPeriod.band,
> rental
> FROM tblSurvey
> INNER JOIN tblClient
> ON tblClient.clientID = tblSurvey.clientID
> INNER JOIN #surveys
> ON #surveys.surveyID = tblSurvey.surveyID
> INNER JOIN #StartOfPeriod
> ON #StartOfPeriod.editionID=tblSurvey.editionID
> WHERE (mileage = @.mileageint OR @.mileageint = 0)
> AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
> AND tblSurvey.editionID >= @.startEdition
> AND tblSurvey.editionID < @.limitEdition
> AND client_alias IS NOT NULL
> AND rental IS NOT NULL
> ) AS averageTemp
> GROUP BY band
> I have tried everything I can think of to make this run quicker, even
though
> it's not really slow, just want to get the best performance possible.
There
> are no way's around the temp tables that I need to use so I am just
wondering
> if there is a better way that I can write the select statement'
> Any help would be great.
> Thanks Phil|||Hi there,
Thanks for the reply, it doesn't look like something that I have tired yet
but I am havinbg a few problems understanding the replacement code that you
put in, is there any chance you could explain it a little further.
Thanks in advance, Phil
"M. de Jong" wrote:

> Have you tried something like this already? Your using select statements
> with selects statements in an Union. I can imagine that your solutions isn
't
> the fasted way to get the information.
> SELECT code
> , [description]
> , clientDescription
> , COUNT(tableTemp.surveyID) AS [count]
> , tblSurvey.clientID AS clientID
> , client_alias AS code
> , CONVERT(varchar(25), client_alias) AS [description], client_desc AS
> clientDescription
> , #StartOfPeriod.band
> , rental
> , CASE clientID WHEN @.clientID THEN 1 ELSE 0 END AS color
> FROM tblSurvey
> <-- Rest of Query -->
> GROUP BY code, [description], clientDescription, band, tableTemp.clientID
> UNION ALL
> SELECT '99' AS code
> ,'IMV' AS [description]
> ,'IMV' AS clientDescription
> ,COUNT(averageTemp.surveyID) AS [count]
> , #StartOfPeriod.band as [band]
> ,AVG(rental) AS value
> ,0 AS color
> fROM tblSurvey
> INNER JOIN tblClient
> AND client_alias IS NOT NULL
> AND rental IS NOT NULL
> <-- Rest of Query -->
>
>
> "Phil" <Phil@.discussions.microsoft.com> wrote in message
> news:CE9CEEE5-9D1E-4735-9A1A-72B01C5105FA@.microsoft.com...
> though
> There
> wondering
>
>|||Hi
A couple of things:
Can this be done with ROLLUP?
#Surveys does not seem to be doing much.
Use owner prefixes and aliases.
Look at the query plans/indexes.
John
"Phil" wrote:

> Hi All,
> I have a little bit of code that looks something like this
> SELECT
> code,
> [description],
> clientDescription,
> band,
> AVG(rental) AS value,
> COUNT(tableTemp.surveyID) AS [count],
> CASE clientID
> WHEN @.clientID THEN 1
> ELSE 0
> END AS color
> FROM
> (
> SELECT tblSurvey.surveyID,
> tblSurvey.clientID AS clientID,
> client_alias AS code,
> CONVERT(varchar(25), client_alias) AS [description],
> client_desc AS clientDescription,
> #StartOfPeriod.band,
> rental
> FROM tblSurvey
> INNER JOIN tblClient
> ON tblClient.clientID = tblSurvey.clientID
> INNER JOIN #surveys
> ON #surveys.surveyID = tblSurvey.surveyID
> INNER JOIN #StartOfPeriod
> ON #StartOfPeriod.editionID=tblSurvey.editionID
> WHERE (mileage = @.mileageint OR @.mileageint = 0)
> AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
> AND tblSurvey.editionID >= @.startEdition
> AND tblSurvey.editionID < @.limitEdition
> AND client_alias IS NOT NULL
> AND rental IS NOT NULL
> ) AS tableTemp
> GROUP BY code,
> [description],
> clientDescription,
> band,
> tableTemp.clientID
> UNION ALL
> SELECT
> '99' AS code,
> 'IMV' AS [description],
> 'IMV' AS clientDescription,
> band,
> AVG(rental) AS value,
> COUNT(averageTemp.surveyID) AS [count],
> 0 AS color
> FROM (SELECT tblSurvey.surveyID,
> #StartOfPeriod.band,
> rental
> FROM tblSurvey
> INNER JOIN tblClient
> ON tblClient.clientID = tblSurvey.clientID
> INNER JOIN #surveys
> ON #surveys.surveyID = tblSurvey.surveyID
> INNER JOIN #StartOfPeriod
> ON #StartOfPeriod.editionID=tblSurvey.editionID
> WHERE (mileage = @.mileageint OR @.mileageint = 0)
> AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
> AND tblSurvey.editionID >= @.startEdition
> AND tblSurvey.editionID < @.limitEdition
> AND client_alias IS NOT NULL
> AND rental IS NOT NULL
> ) AS averageTemp
> GROUP BY band
> I have tried everything I can think of to make this run quicker, even thou
gh
> it's not really slow, just want to get the best performance possible. Ther
e
> are no way's around the temp tables that I need to use so I am just wonder
ing
> if there is a better way that I can write the select statement'
> Any help would be great.
> Thanks Phil|||Hi,
Thanks for the reply, you are correct #surveys doesn't do much, when you say
use ownder alias and prefixes, I am not quite sure what you mean, sorry abou
t
that. I have looked at the query plan and the biggest percentage comes from
Index scans but I am afriad I am not able to change them. I am also not sur
e
how ROLLUP would be of any help.
Sorry about all the question!!!
"John Bell" wrote:
> Hi
> A couple of things:
> Can this be done with ROLLUP?
> #Surveys does not seem to be doing much.
> Use owner prefixes and aliases.
> Look at the query plans/indexes.
> John
> "Phil" wrote:
>|||Hi
Your UNIONED line seems to be a summary line, in which case using
ROLLUP could do that for you.
Using table aliases throughout would make everything clearer (you don't
need the column aliases in the unioned part!).
Using the owner prefix will minimise name resolution as described in
the paragraph
"Fully Qualify Database Objects" See
http://msdn.microsoft.com/library/d...
etchapt14.asp
John
Phil wrote:
> Hi,
> Thanks for the reply, you are correct #surveys doesn't do much, when
you say
> use ownder alias and prefixes, I am not quite sure what you mean,
sorry about
> that. I have looked at the query plan and the biggest percentage
comes from
> Index scans but I am afriad I am not able to change them. I am also
not sure
> how ROLLUP would be of any help.
> Sorry about all the question!!!
> "John Bell" wrote:
>
even though
possible. There
just wondering|||Phil,
There are a few things you can do.
1) What M. de Jong tried to explain, is that your current query is
something like this:
SELECT ... FROM (
SELECT ... FROM <T1> GROUP BY <GB1>
UNION ALL
SELECT ... FROM <T2> GROUP BY <GB2>
) GROUP BY <GB3>
This query can be rewritten as:
SELECT ... FROM <1> GROUP BY <GB1>,<GB3>
UNION ALL
SELECT ... FROM <2> GROUP BY <GB2>,<GB3>
This eliminates one GROUP BY action.
2) As Phil mentioned, table #Surveys doesn't seem to do much. But if it
is necessary, and there is a one-to-one relation with tblSurvey, then
you could remove the INNER JOIN with #Surveys, and add
AND EXISTS (SELECT 1 FROM #Surveys WHERE #surveys.surveyID =
tblSurvey.surveyID)
and see if that makes any difference in performance.
3) If the columns mileage and age_in_months do not contain NULLs, then
you could rewrite the predicates with these columns in such a way that
the OR is removed and an index s could potentially be used. For
example, you could rewrite:
AND (mileage = @.mileageint OR @.mileageint = 0)
with:
AND mileage BETWEEN COALESCE(NULLIF(@.mileageint,0), -2147483648)
AND COALESCE(NULLIF(@.mileageint,0), 2147483647)
4) Make sure all join keys are indexed. In addition, you could try the
Index Tuning Wizard.
Note that this is based on what you posted. Since you did not post any
DDL, we cannot see for all columns from which table they are selected
(the missing table aliases John was talking about).
Hope this helps,
Gert-Jan
Phil wrote:
> Hi All,
> I have a little bit of code that looks something like this
> SELECT
> code,
> [description],
> clientDescription,
> band,
> AVG(rental) AS value,
> COUNT(tableTemp.surveyID) AS [count],
> CASE clientID
> WHEN @.clientID THEN 1
> ELSE 0
> END AS color
> FROM
> (
> SELECT tblSurvey.surveyID,
> tblSurvey.clientID AS clientID,
> client_alias AS code,
> CONVERT(varchar(25), client_alias) AS [description],
> client_desc AS clientDescription,
> #StartOfPeriod.band,
> rental
> FROM tblSurvey
> INNER JOIN tblClient
> ON tblClient.clientID = tblSurvey.clientID
> INNER JOIN #surveys
> ON #surveys.surveyID = tblSurvey.surveyID
> INNER JOIN #StartOfPeriod
> ON #StartOfPeriod.editionID=tblSurvey.editionID
> WHERE (mileage = @.mileageint OR @.mileageint = 0)
> AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
> AND tblSurvey.editionID >= @.startEdition
> AND tblSurvey.editionID < @.limitEdition
> AND client_alias IS NOT NULL
> AND rental IS NOT NULL
> ) AS tableTemp
> GROUP BY code,
> [description],
> clientDescription,
> band,
> tableTemp.clientID
> UNION ALL
> SELECT
> '99' AS code,
> 'IMV' AS [description],
> 'IMV' AS clientDescription,
> band,
> AVG(rental) AS value,
> COUNT(averageTemp.surveyID) AS [count],
> 0 AS color
> FROM (SELECT tblSurvey.surveyID,
> #StartOfPeriod.band,
> rental
> FROM tblSurvey
> INNER JOIN tblClient
> ON tblClient.clientID = tblSurvey.clientID
> INNER JOIN #surveys
> ON #surveys.surveyID = tblSurvey.surveyID
> INNER JOIN #StartOfPeriod
> ON #StartOfPeriod.editionID=tblSurvey.editionID
> WHERE (mileage = @.mileageint OR @.mileageint = 0)
> AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
> AND tblSurvey.editionID >= @.startEdition
> AND tblSurvey.editionID < @.limitEdition
> AND client_alias IS NOT NULL
> AND rental IS NOT NULL
> ) AS averageTemp
> GROUP BY band
> I have tried everything I can think of to make this run quicker, even thou
gh
> it's not really slow, just want to get the best performance possible. Ther
e
> are no way's around the temp tables that I need to use so I am just wonder
ing
> if there is a better way that I can write the select statement'
> Any help would be great.
> Thanks Phil|||Hi Phil
A possible alternative to Gert-Jan's point2 would be to change the way
that #surveys is constructed so that it includes (when constructed) the
information you re-select from tblSurveys and therefore tblSurveys can
be excluded from the statement.
John
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Hi there,
I would just like to say a quick thanks to everyone that has posted a
reply to my question, I have now managed to get it working better with a
combination of your solutions.
Sorry I didn't post exactly what you needed but I will rememeber for
next time, thanks again.
Phil
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!