Hi All
win 2k pro sql 2k IIS 5 ASP-vbscript
I got a stored proc which basically returns a set of records depending
on a category ID (or all if passed ID =0) - see below
The FAQ Category below is retuned mostly order by Title but in some
other areas of my site I need to return it orded by CatID.
I was wondering whats the best way to do this as I cant seem to pass a
column name to the procedure and put "ORDER BY @.SortOrder"
These are the other ways I thought of.
1) create a new procedure the same with order by catID.
2) pass a number and use CASE - but I get error 1008
eg ORDER BY CASE WHEN @.Order=0 THEN Title ELSE CatID END
I can see this becoming a common problem so wouldn't like to do #1
above as that would require alot of maintence to keep them the same if
I changed any of the table design.
Thanks for any help.
Al
CREATE PROCEDURE dbo.usp_Help_FindFAQCategoryByCatID
@.SelCatID TINYINT = 0
AS
SET NOCOUNT ON
-- Return all the FAQ Categories with Cat ID of the selected Category
SELECT CatID, ParentID, Title, FaqCount
FROM Help_FAQ_Categories
WHERE CatID= CASE WHEN @.SelCatID=0 THEN CatID ELSE @.SelCatID END
ORDER BY Title
GOTry
ORDER BY CASE WHEN @.sort = 'col1' THEN col1 END ASC,
"Harag" <harag@.softhome.net> wrote in message
news:3v4mlvo18e9vbjisl08jqrs9m75pc240gd@.4ax.com...
> Hi All
> win 2k pro sql 2k IIS 5 ASP-vbscript
> I got a stored proc which basically returns a set of records depending
> on a category ID (or all if passed ID =0) - see below
> The FAQ Category below is retuned mostly order by Title but in some
> other areas of my site I need to return it orded by CatID.
> I was wondering whats the best way to do this as I cant seem to pass a
> column name to the procedure and put "ORDER BY @.SortOrder"
> These are the other ways I thought of.
> 1) create a new procedure the same with order by catID.
> 2) pass a number and use CASE - but I get error 1008
> eg ORDER BY CASE WHEN @.Order=0 THEN Title ELSE CatID END
>
> I can see this becoming a common problem so wouldn't like to do #1
> above as that would require alot of maintence to keep them the same if
> I changed any of the table design.
> Thanks for any help.
> Al
>
> CREATE PROCEDURE dbo.usp_Help_FindFAQCategoryByCatID
> @.SelCatID TINYINT = 0
> AS
> SET NOCOUNT ON
> -- Return all the FAQ Categories with Cat ID of the selected Category
> SELECT CatID, ParentID, Title, FaqCount
> FROM Help_FAQ_Categories
> WHERE CatID=> CASE WHEN @.SelCatID=0 THEN CatID ELSE @.SelCatID END
> ORDER BY Title
> GO
>|||Harag,
it is almost correct. However, you cannot mix data types without
explicit conversions. The typical solution to this type of problem is to
use an ORDER BY column for each sort order:
ORDER BY CASE WHEN @.Order=0 THEN Title END,
CASE WHEN @.Order=1 THEN CatID END
Gert-Jan
Harag wrote:
> Hi All
> win 2k pro sql 2k IIS 5 ASP-vbscript
> I got a stored proc which basically returns a set of records depending
> on a category ID (or all if passed ID =0) - see below
> The FAQ Category below is retuned mostly order by Title but in some
> other areas of my site I need to return it orded by CatID.
> I was wondering whats the best way to do this as I cant seem to pass a
> column name to the procedure and put "ORDER BY @.SortOrder"
> These are the other ways I thought of.
> 1) create a new procedure the same with order by catID.
> 2) pass a number and use CASE - but I get error 1008
> eg ORDER BY CASE WHEN @.Order=0 THEN Title ELSE CatID END
> I can see this becoming a common problem so wouldn't like to do #1
> above as that would require alot of maintence to keep them the same if
> I changed any of the table design.
> Thanks for any help.
> Al
> CREATE PROCEDURE dbo.usp_Help_FindFAQCategoryByCatID
> @.SelCatID TINYINT = 0
> AS
> SET NOCOUNT ON
> -- Return all the FAQ Categories with Cat ID of the selected Category
> SELECT CatID, ParentID, Title, FaqCount
> FROM Help_FAQ_Categories
> WHERE CatID=> CASE WHEN @.SelCatID=0 THEN CatID ELSE @.SelCatID END
> ORDER BY Title
> GO|||Cool thanks guys this is what I wanted and will save alot of
maintence. :)
Thanks again
Al
On Sun, 07 Sep 2003 14:01:35 +0200, Gert-Jan Strik
<sorry@.toomuchspamalready.nl> wrote:
>Harag,
>it is almost correct. However, you cannot mix data types without
>explicit conversions. The typical solution to this type of problem is to
>use an ORDER BY column for each sort order:
>ORDER BY CASE WHEN @.Order=0 THEN Title END,
> CASE WHEN @.Order=1 THEN CatID END
>Gert-Jan
>
>Harag wrote:
>> Hi All
>> win 2k pro sql 2k IIS 5 ASP-vbscript
>> I got a stored proc which basically returns a set of records depending
>> on a category ID (or all if passed ID =0) - see below
>> The FAQ Category below is retuned mostly order by Title but in some
>> other areas of my site I need to return it orded by CatID.
>> I was wondering whats the best way to do this as I cant seem to pass a
>> column name to the procedure and put "ORDER BY @.SortOrder"
>> These are the other ways I thought of.
>> 1) create a new procedure the same with order by catID.
>> 2) pass a number and use CASE - but I get error 1008
>> eg ORDER BY CASE WHEN @.Order=0 THEN Title ELSE CatID END
>> I can see this becoming a common problem so wouldn't like to do #1
>> above as that would require alot of maintence to keep them the same if
>> I changed any of the table design.
>> Thanks for any help.
>> Al
>> CREATE PROCEDURE dbo.usp_Help_FindFAQCategoryByCatID
>> @.SelCatID TINYINT = 0
>> AS
>> SET NOCOUNT ON
>> -- Return all the FAQ Categories with Cat ID of the selected Category
>> SELECT CatID, ParentID, Title, FaqCount
>> FROM Help_FAQ_Categories
>> WHERE CatID=>> CASE WHEN @.SelCatID=0 THEN CatID ELSE @.SelCatID END
>> ORDER BY Title
>> GO
Showing posts with label returns. Show all posts
Showing posts with label returns. Show all posts
Saturday, February 25, 2012
Thursday, February 16, 2012
Changing Explain Plan
Hi,
We have a stored proc that generally returns in about 4-5 seconds. Over the
course of a few days (2-5) the stored proc starts returning in about 50 sec
onds. The change is sudden, not a gradual degradation. The explain plan ha
s changed. If I then add a
hint to the main query (loop join), compile it, take the hint back out, comp
ile it (ie it is now exactly the same as it was), it then starts returning i
n 4-5 seconds once again.
We have automatic creation and update of statisics turned on. The profile o
f the data doesn't change that much day-to-day.
Why is it so? How to stop SQL Server from changing the plans for this query
?
TIA,
SJTHi SJT,
Sudden performance degrade might might be due to "Fragmetation". Can you
execute the DBCC SHOWCONTIG on all tables and identify the tables
which is fragmented. For all fragmented table you could run DBCC DBREINDEX
or DBCC INDEXDEFRAG to remove fragmetation.
See the details of DBCC SHOWCONTIG, DBCC DBREINDEX and DBCC INDEXDEFRAG in
boks online.
How to stop SQL Server from changing the plans for this query?
See "Execution Plan Caching and Reuse" in books online.
Thanks
Hari
MCDBA
"SJT" <SJT@.discussions.microsoft.com> wrote in message
news:E48CD7B7-28C0-4A13-99C7-5ED85FB7E5D8@.microsoft.com...
> Hi,
> We have a stored proc that generally returns in about 4-5 seconds. Over
the course of a few days (2-5) the stored proc starts returning in about 50
seconds. The change is sudden, not a gradual degradation. The explain plan
has changed. If I then add a hint to the main query (loop join), compile
it, take the hint back out, compile it (ie it is now exactly the same as it
was), it then starts returning in 4-5 seconds once again.
> We have automatic creation and update of statisics turned on. The profile
of the data doesn't change that much day-to-day.
> Why is it so? How to stop SQL Server from changing the plans for this
query?
> TIA,
> SJT|||SJT,
instead of rewriting the query two times, see if "sp_recompile <name of
SP>" achieves the same thing.
Also, search Google for "parameter sniffing", and see if that is causing
your procedure's peformance fluctuation.
Hope this helps,
Gert-Jan
SJT wrote:
> Hi,
> We have a stored proc that generally returns in about 4-5 seconds. Over the cours
e of a few days (2-5) the stored proc starts returning in about 50 seconds. The cha
nge is sudden, not a gradual degradation. The explain plan has changed. If I then
add
a hint to the main query (loop join), compile it, take the hint back out, compile it (ie it
is now exactly the same as it was), it then starts returning in 4-5 seconds once again.[vbco
l=seagreen]
> We have automatic creation and update of statisics turned on. The profile
of the data doesn't change that much day-to-day.
> Why is it so? How to stop SQL Server from changing the plans for this que
ry?
> TIA,
> SJT[/vbcol]
(Please reply only to the newsgroup)
We have a stored proc that generally returns in about 4-5 seconds. Over the
course of a few days (2-5) the stored proc starts returning in about 50 sec
onds. The change is sudden, not a gradual degradation. The explain plan ha
s changed. If I then add a
hint to the main query (loop join), compile it, take the hint back out, comp
ile it (ie it is now exactly the same as it was), it then starts returning i
n 4-5 seconds once again.
We have automatic creation and update of statisics turned on. The profile o
f the data doesn't change that much day-to-day.
Why is it so? How to stop SQL Server from changing the plans for this query
?
TIA,
SJTHi SJT,
Sudden performance degrade might might be due to "Fragmetation". Can you
execute the DBCC SHOWCONTIG on all tables and identify the tables
which is fragmented. For all fragmented table you could run DBCC DBREINDEX
or DBCC INDEXDEFRAG to remove fragmetation.
See the details of DBCC SHOWCONTIG, DBCC DBREINDEX and DBCC INDEXDEFRAG in
boks online.
How to stop SQL Server from changing the plans for this query?
See "Execution Plan Caching and Reuse" in books online.
Thanks
Hari
MCDBA
"SJT" <SJT@.discussions.microsoft.com> wrote in message
news:E48CD7B7-28C0-4A13-99C7-5ED85FB7E5D8@.microsoft.com...
> Hi,
> We have a stored proc that generally returns in about 4-5 seconds. Over
the course of a few days (2-5) the stored proc starts returning in about 50
seconds. The change is sudden, not a gradual degradation. The explain plan
has changed. If I then add a hint to the main query (loop join), compile
it, take the hint back out, compile it (ie it is now exactly the same as it
was), it then starts returning in 4-5 seconds once again.
> We have automatic creation and update of statisics turned on. The profile
of the data doesn't change that much day-to-day.
> Why is it so? How to stop SQL Server from changing the plans for this
query?
> TIA,
> SJT|||SJT,
instead of rewriting the query two times, see if "sp_recompile <name of
SP>" achieves the same thing.
Also, search Google for "parameter sniffing", and see if that is causing
your procedure's peformance fluctuation.
Hope this helps,
Gert-Jan
SJT wrote:
> Hi,
> We have a stored proc that generally returns in about 4-5 seconds. Over the cours
e of a few days (2-5) the stored proc starts returning in about 50 seconds. The cha
nge is sudden, not a gradual degradation. The explain plan has changed. If I then
add
a hint to the main query (loop join), compile it, take the hint back out, compile it (ie it
is now exactly the same as it was), it then starts returning in 4-5 seconds once again.[vbco
l=seagreen]
> We have automatic creation and update of statisics turned on. The profile
of the data doesn't change that much day-to-day.
> Why is it so? How to stop SQL Server from changing the plans for this que
ry?
> TIA,
> SJT[/vbcol]
(Please reply only to the newsgroup)
Changing Explain Plan
Hi,
We have a stored proc that generally returns in about 4-5 seconds. Over the course of a few days (2-5) the stored proc starts returning in about 50 seconds. The change is sudden, not a gradual degradation. The explain plan has changed. If I then add a
hint to the main query (loop join), compile it, take the hint back out, compile it (ie it is now exactly the same as it was), it then starts returning in 4-5 seconds once again.
We have automatic creation and update of statisics turned on. The profile of the data doesn't change that much day-to-day.
Why is it so? How to stop SQL Server from changing the plans for this query?
TIA,
SJT
Hi SJT,
Sudden performance degrade might might be due to "Fragmetation". Can you
execute the DBCC SHOWCONTIG on all tables and identify the tables
which is fragmented. For all fragmented table you could run DBCC DBREINDEX
or DBCC INDEXDEFRAG to remove fragmetation.
See the details of DBCC SHOWCONTIG, DBCC DBREINDEX and DBCC INDEXDEFRAG in
boks online.
How to stop SQL Server from changing the plans for this query?
See "Execution Plan Caching and Reuse" in books online.
Thanks
Hari
MCDBA
"SJT" <SJT@.discussions.microsoft.com> wrote in message
news:E48CD7B7-28C0-4A13-99C7-5ED85FB7E5D8@.microsoft.com...
> Hi,
> We have a stored proc that generally returns in about 4-5 seconds. Over
the course of a few days (2-5) the stored proc starts returning in about 50
seconds. The change is sudden, not a gradual degradation. The explain plan
has changed. If I then add a hint to the main query (loop join), compile
it, take the hint back out, compile it (ie it is now exactly the same as it
was), it then starts returning in 4-5 seconds once again.
> We have automatic creation and update of statisics turned on. The profile
of the data doesn't change that much day-to-day.
> Why is it so? How to stop SQL Server from changing the plans for this
query?
> TIA,
> SJT
|||SJT,
instead of rewriting the query two times, see if "sp_recompile <name of
SP>" achieves the same thing.
Also, search Google for "parameter sniffing", and see if that is causing
your procedure's peformance fluctuation.
Hope this helps,
Gert-Jan
SJT wrote:
> Hi,
> We have a stored proc that generally returns in about 4-5 seconds. Over the course of a few days (2-5) the stored proc starts returning in about 50 seconds. The change is sudden, not a gradual degradation. The explain plan has changed. If I then add
a hint to the main query (loop join), compile it, take the hint back out, compile it (ie it is now exactly the same as it was), it then starts returning in 4-5 seconds once again.
> We have automatic creation and update of statisics turned on. The profile of the data doesn't change that much day-to-day.
> Why is it so? How to stop SQL Server from changing the plans for this query?
> TIA,
> SJT
(Please reply only to the newsgroup)
We have a stored proc that generally returns in about 4-5 seconds. Over the course of a few days (2-5) the stored proc starts returning in about 50 seconds. The change is sudden, not a gradual degradation. The explain plan has changed. If I then add a
hint to the main query (loop join), compile it, take the hint back out, compile it (ie it is now exactly the same as it was), it then starts returning in 4-5 seconds once again.
We have automatic creation and update of statisics turned on. The profile of the data doesn't change that much day-to-day.
Why is it so? How to stop SQL Server from changing the plans for this query?
TIA,
SJT
Hi SJT,
Sudden performance degrade might might be due to "Fragmetation". Can you
execute the DBCC SHOWCONTIG on all tables and identify the tables
which is fragmented. For all fragmented table you could run DBCC DBREINDEX
or DBCC INDEXDEFRAG to remove fragmetation.
See the details of DBCC SHOWCONTIG, DBCC DBREINDEX and DBCC INDEXDEFRAG in
boks online.
How to stop SQL Server from changing the plans for this query?
See "Execution Plan Caching and Reuse" in books online.
Thanks
Hari
MCDBA
"SJT" <SJT@.discussions.microsoft.com> wrote in message
news:E48CD7B7-28C0-4A13-99C7-5ED85FB7E5D8@.microsoft.com...
> Hi,
> We have a stored proc that generally returns in about 4-5 seconds. Over
the course of a few days (2-5) the stored proc starts returning in about 50
seconds. The change is sudden, not a gradual degradation. The explain plan
has changed. If I then add a hint to the main query (loop join), compile
it, take the hint back out, compile it (ie it is now exactly the same as it
was), it then starts returning in 4-5 seconds once again.
> We have automatic creation and update of statisics turned on. The profile
of the data doesn't change that much day-to-day.
> Why is it so? How to stop SQL Server from changing the plans for this
query?
> TIA,
> SJT
|||SJT,
instead of rewriting the query two times, see if "sp_recompile <name of
SP>" achieves the same thing.
Also, search Google for "parameter sniffing", and see if that is causing
your procedure's peformance fluctuation.
Hope this helps,
Gert-Jan
SJT wrote:
> Hi,
> We have a stored proc that generally returns in about 4-5 seconds. Over the course of a few days (2-5) the stored proc starts returning in about 50 seconds. The change is sudden, not a gradual degradation. The explain plan has changed. If I then add
a hint to the main query (loop join), compile it, take the hint back out, compile it (ie it is now exactly the same as it was), it then starts returning in 4-5 seconds once again.
> We have automatic creation and update of statisics turned on. The profile of the data doesn't change that much day-to-day.
> Why is it so? How to stop SQL Server from changing the plans for this query?
> TIA,
> SJT
(Please reply only to the newsgroup)
Changing Explain Plan
Hi,
We have a stored proc that generally returns in about 4-5 seconds. Over the course of a few days (2-5) the stored proc starts returning in about 50 seconds. The change is sudden, not a gradual degradation. The explain plan has changed. If I then add a hint to the main query (loop join), compile it, take the hint back out, compile it (ie it is now exactly the same as it was), it then starts returning in 4-5 seconds once again.
We have automatic creation and update of statisics turned on. The profile of the data doesn't change that much day-to-day.
Why is it so? How to stop SQL Server from changing the plans for this query?
TIA,
SJTHi SJT,
Sudden performance degrade might might be due to "Fragmetation". Can you
execute the DBCC SHOWCONTIG on all tables and identify the tables
which is fragmented. For all fragmented table you could run DBCC DBREINDEX
or DBCC INDEXDEFRAG to remove fragmetation.
See the details of DBCC SHOWCONTIG, DBCC DBREINDEX and DBCC INDEXDEFRAG in
boks online.
How to stop SQL Server from changing the plans for this query?
See "Execution Plan Caching and Reuse" in books online.
Thanks
Hari
MCDBA
"SJT" <SJT@.discussions.microsoft.com> wrote in message
news:E48CD7B7-28C0-4A13-99C7-5ED85FB7E5D8@.microsoft.com...
> Hi,
> We have a stored proc that generally returns in about 4-5 seconds. Over
the course of a few days (2-5) the stored proc starts returning in about 50
seconds. The change is sudden, not a gradual degradation. The explain plan
has changed. If I then add a hint to the main query (loop join), compile
it, take the hint back out, compile it (ie it is now exactly the same as it
was), it then starts returning in 4-5 seconds once again.
> We have automatic creation and update of statisics turned on. The profile
of the data doesn't change that much day-to-day.
> Why is it so? How to stop SQL Server from changing the plans for this
query?
> TIA,
> SJT|||SJT,
instead of rewriting the query two times, see if "sp_recompile <name of
SP>" achieves the same thing.
Also, search Google for "parameter sniffing", and see if that is causing
your procedure's peformance fluctuation.
Hope this helps,
Gert-Jan
SJT wrote:
> Hi,
> We have a stored proc that generally returns in about 4-5 seconds. Over the course of a few days (2-5) the stored proc starts returning in about 50 seconds. The change is sudden, not a gradual degradation. The explain plan has changed. If I then add a hint to the main query (loop join), compile it, take the hint back out, compile it (ie it is now exactly the same as it was), it then starts returning in 4-5 seconds once again.
> We have automatic creation and update of statisics turned on. The profile of the data doesn't change that much day-to-day.
> Why is it so? How to stop SQL Server from changing the plans for this query?
> TIA,
> SJT
--
(Please reply only to the newsgroup)
We have a stored proc that generally returns in about 4-5 seconds. Over the course of a few days (2-5) the stored proc starts returning in about 50 seconds. The change is sudden, not a gradual degradation. The explain plan has changed. If I then add a hint to the main query (loop join), compile it, take the hint back out, compile it (ie it is now exactly the same as it was), it then starts returning in 4-5 seconds once again.
We have automatic creation and update of statisics turned on. The profile of the data doesn't change that much day-to-day.
Why is it so? How to stop SQL Server from changing the plans for this query?
TIA,
SJTHi SJT,
Sudden performance degrade might might be due to "Fragmetation". Can you
execute the DBCC SHOWCONTIG on all tables and identify the tables
which is fragmented. For all fragmented table you could run DBCC DBREINDEX
or DBCC INDEXDEFRAG to remove fragmetation.
See the details of DBCC SHOWCONTIG, DBCC DBREINDEX and DBCC INDEXDEFRAG in
boks online.
How to stop SQL Server from changing the plans for this query?
See "Execution Plan Caching and Reuse" in books online.
Thanks
Hari
MCDBA
"SJT" <SJT@.discussions.microsoft.com> wrote in message
news:E48CD7B7-28C0-4A13-99C7-5ED85FB7E5D8@.microsoft.com...
> Hi,
> We have a stored proc that generally returns in about 4-5 seconds. Over
the course of a few days (2-5) the stored proc starts returning in about 50
seconds. The change is sudden, not a gradual degradation. The explain plan
has changed. If I then add a hint to the main query (loop join), compile
it, take the hint back out, compile it (ie it is now exactly the same as it
was), it then starts returning in 4-5 seconds once again.
> We have automatic creation and update of statisics turned on. The profile
of the data doesn't change that much day-to-day.
> Why is it so? How to stop SQL Server from changing the plans for this
query?
> TIA,
> SJT|||SJT,
instead of rewriting the query two times, see if "sp_recompile <name of
SP>" achieves the same thing.
Also, search Google for "parameter sniffing", and see if that is causing
your procedure's peformance fluctuation.
Hope this helps,
Gert-Jan
SJT wrote:
> Hi,
> We have a stored proc that generally returns in about 4-5 seconds. Over the course of a few days (2-5) the stored proc starts returning in about 50 seconds. The change is sudden, not a gradual degradation. The explain plan has changed. If I then add a hint to the main query (loop join), compile it, take the hint back out, compile it (ie it is now exactly the same as it was), it then starts returning in 4-5 seconds once again.
> We have automatic creation and update of statisics turned on. The profile of the data doesn't change that much day-to-day.
> Why is it so? How to stop SQL Server from changing the plans for this query?
> TIA,
> SJT
--
(Please reply only to the newsgroup)
Subscribe to:
Posts (Atom)