August 10, 2015 at 7:33 am
Sean Lange (8/10/2015)
mrtran0404 (8/10/2015)
Sean Lange (8/10/2015)
ChrisM@Work (8/10/2015)
John Mitchell-245523 (8/10/2015)
You're selecting every row from the table, so it's not going to be all that quick. Get rid of your whole WHERE clause - it's just noise. A non-clustered index on Price would possibly speed things up a little, or maybe even changing the clustered index to that column. But you'd need to consider the effect that would have on inserts, updates and other queries.John
Price >= case when 0 = 0 then Price else 0 endThis has to be a prank.
Either a prank or somebody "out clevered" themselves into thinking this is really great. That was a great start to week and that line of code nearly made me spit out my coffee. :hehe:
Hi sir,
thank for your reply and your time, i don prank may be my query string posted is wrong but realdy im having problem with my database when i'm select data from my table
thanks
Did you try the suggestions from Chris M?
Yes i tried but result i geting is very long time to get data :angry:
August 10, 2015 at 7:37 am
mrtran0404 (8/10/2015)
Sean Lange (8/10/2015)
mrtran0404 (8/10/2015)
Sean Lange (8/10/2015)
ChrisM@Work (8/10/2015)
John Mitchell-245523 (8/10/2015)
You're selecting every row from the table, so it's not going to be all that quick. Get rid of your whole WHERE clause - it's just noise. A non-clustered index on Price would possibly speed things up a little, or maybe even changing the clustered index to that column. But you'd need to consider the effect that would have on inserts, updates and other queries.John
Price >= case when 0 = 0 then Price else 0 endThis has to be a prank.
Either a prank or somebody "out clevered" themselves into thinking this is really great. That was a great start to week and that line of code nearly made me spit out my coffee. :hehe:
Hi sir,
thank for your reply and your time, i don prank may be my query string posted is wrong but realdy im having problem with my database when i'm select data from my table
thanks
Did you try the suggestions from Chris M?
Yes i tried but result i geting is very long time to get data :angry:
Can you post what you've tried? There may be something you've missed.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 10, 2015 at 7:40 am
mrtran0404 (8/10/2015)
Sean Lange (8/10/2015)
mrtran0404 (8/10/2015)
Sean Lange (8/10/2015)
ChrisM@Work (8/10/2015)
John Mitchell-245523 (8/10/2015)
You're selecting every row from the table, so it's not going to be all that quick. Get rid of your whole WHERE clause - it's just noise. A non-clustered index on Price would possibly speed things up a little, or maybe even changing the clustered index to that column. But you'd need to consider the effect that would have on inserts, updates and other queries.John
Price >= case when 0 = 0 then Price else 0 endThis has to be a prank.
Either a prank or somebody "out clevered" themselves into thinking this is really great. That was a great start to week and that line of code nearly made me spit out my coffee. :hehe:
Hi sir,
thank for your reply and your time, i don prank may be my query string posted is wrong but realdy im having problem with my database when i'm select data from my table
thanks
Did you try the suggestions from Chris M?
Yes i tried but result i geting is very long time to get data :angry:
Well here is the challenge. So far you have posted two drastically different queries. Which one are you using? Or are trying some hybrid that contains that suggestions from Chris? The bigger problem is that we don't have any idea what your tables and indexing looks like. We need a lot more information here before we can really help. Please take a look at this article which explains what kind of information is needed in order to help with performance problems. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/%5B/url%5D
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 10, 2015 at 8:05 am
I took one look at the stored procedure and it definitely needed work. Take a look at the way it's been re-written by comparing it to the original.
There appears to be a boo-boo where the Name and image fields get turned around in one of the four possible resulting queries, and I chose to go
with correcting it to have the same set of fields as the other 3, as I couldn't see any obvious reason NOT to make the correction. Given the responses
you have so far, this appears to address that stuff as well:
/****** Object: StoredProcedure [dbo].[GetSimFilter] Script Date: 08/10/2015 15:53:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetSimFilter]
@number varchar(20),
@nwId int,
@stId int,
@pageIndex int,
@pageSize int,
@price_min decimal,
@price_max decimal,
@orderBy int,
@numCount int,
@sup_ID int = -1
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets FROM
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF LEN(@number) <> 0
BEGIN
IF CHARINDEX('*', @number) = 0
BEGIN
SET @number = '%' + @number + '%'
END
SET @number = REPLACE(@number, '*', '%')
IF @orderBy = 0
BEGIN
SELECT p.[Number], N.[image], N.[Name], T.[Name] AS SimType, p.Price
FROM (
SELECT [Number], NetWork_ID, SimType_ID, Price, ROW_NUMBER() OVER (ORDER BY Price DESC) AS stt
FROM SIM_CLIENT AS S
WHERE Number LIKE @number
AND ISNULL([NetWork_ID], 0) = ISNULL(NULLIF(@nwId, 0), ISNULL([NetWork_ID], 0))
AND ISNULL([SimType_ID],0) = ISNULL(NULLIF(@stId, 0), ISNULL([SimType_ID], 0))
AND Supplier_ID = ISNULL(NULLIF(@sup_ID, -1), Supplier_ID)
AND LEN([Number]) = ISNULL(NULLIF(@numCount, 0), LEN([Number]))
AND Price >= ISNULL(NULLIF(@price_min, 0), Price)
AND Price <= ISNULL(NULLIF(@price_max, 0), Price)
) AS p
LEFT OUTER JOIN [dbo].[NetWork] AS N
ON p.NetWork_ID = N.ID
LEFT OUTER JOIN [dbo].SimType AS T
ON p.SimType_ID = T.ID
WHERE p.stt > @pageSize * (@pageIndex - 1)
AND p.stt <= @pageSize * @pageIndex
END
ELSE
BEGIN
SELECT TOP 200 [Number], N.[Name], N.[image], T.[Name] AS SimType, p.Price
FROM (
SELECT [Number], NetWork_ID, SimType_ID, Price, ROW_NUMBER() OVER (ORDER BY Price) AS stt
FROM SIM_CLIENT AS S
WHERE Number LIKE @number
AND ISNULL([NetWork_ID], 0) = ISNULL(NULLIF(@nwId, 0), ISNULL([NetWork_ID], 0))
AND ISNULL([SimType_ID],0) = ISNULL(NULLIF(@stId, 0), ISNULL([SimType_ID], 0))
AND Supplier_ID = ISNULL(NULLIF(@sup_ID, -1), Supplier_ID)
AND LEN([Number]) = ISNULL(NULLIF(@numCount, 0), LEN([Number]))
AND Price >= ISNULL(NULLIF(@price_min, 0), Price)
AND Price <= ISNULL(NULLIF(@price_max, 0), Price)
) AS p
LEFT OUTER JOIN [dbo].[NetWork] AS N
ON p.NetWork_ID = N.ID
LEFT OUTER JOIN [dbo].SimType AS T
ON p.SimType_ID = T.ID
WHERE p.stt > @pageSize * (@pageIndex - 1)
AND p.stt <= @pageSize * @pageIndex
END
-- Insert statements for procedure here
END
ELSE
BEGIN
IF @orderBy = 0
BEGIN
SELECT [Number], N.[Name], N.[image], T.[Name] AS SimType, p.Price
FROM (
SELECT [Number], NetWork_ID, SimType_ID, Price, ROW_NUMBER() OVER (ORDER BY Price DESC) AS stt
FROM SIM_CLIENT AS S
WHERE ISNULL([NetWork_ID], 0) = ISNULL(NULLIF(@nwId, 0), ISNULL([NetWork_ID], 0))
AND ISNULL([SimType_ID],0) = ISNULL(NULLIF(@stId, 0), ISNULL([SimType_ID], 0))
AND Supplier_ID = ISNULL(NULLIF(@sup_ID, -1), Supplier_ID)
AND LEN([Number]) = ISNULL(NULLIF(@numCount, 0), LEN([Number]))
AND Price >= ISNULL(NULLIF(@price_min, 0), Price)
AND Price <= ISNULL(NULLIF(@price_max, 0), Price)
) AS p
LEFT OUTER JOIN [dbo].[NetWork] AS N
ON p.NetWork_ID = N.ID
LEFT OUTER JOIN [dbo].SimType AS T
ON p.SimType_ID = T.ID
WHERE p.stt > @pageSize * (@pageIndex - 1)
AND p.stt <= @pageSize * @pageIndex
END
ELSE
BEGIN
SELECT [Number], N.[Name], N.[image], T.[Name] AS SimType, p.Price
FROM (
SELECT [Number], NetWork_ID, SimType_ID, Price, ROW_NUMBER() OVER (ORDER BY Price) AS stt
FROM SIM_CLIENT AS S
WHERE ISNULL([NetWork_ID], 0) = ISNULL(NULLIF(@nwId, 0), ISNULL([NetWork_ID], 0))
AND ISNULL([SimType_ID],0) = ISNULL(NULLIF(@stId, 0), ISNULL([SimType_ID], 0))
AND Supplier_ID = ISNULL(NULLIF(@sup_ID, -1), Supplier_ID)
AND LEN([Number]) = ISNULL(NULLIF(@numCount, 0), LEN([Number]))
AND Price >= ISNULL(NULLIF(@price_min, 0), Price)
AND Price <= ISNULL(NULLIF(@price_max, 0), Price)
) AS p
LEFT OUTER JOIN [dbo].[NetWork] AS N
ON p.NetWork_ID = N.ID
LEFT OUTER JOIN [dbo].SimType AS T
ON p.SimType_ID = T.ID
WHERE p.stt > @pageSize * (@pageIndex - 1)
AND p.stt <=@pageSize * @pageIndex
END
END
END
As I have no data to test with, I've no means to ensure this will help, but my guess is that it probably won't hurt.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 10, 2015 at 7:20 pm
sgmunson (8/10/2015)
I took one look at the stored procedure and it definitely needed work. Take a look at the way it's been re-written by comparing it to the original.There appears to be a boo-boo where the Name and image fields get turned around in one of the four possible resulting queries, and I chose to go
with correcting it to have the same set of fields as the other 3, as I couldn't see any obvious reason NOT to make the correction. Given the responses
you have so far, this appears to address that stuff as well:
/****** Object: StoredProcedure [dbo].[GetSimFilter] Script Date: 08/10/2015 15:53:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetSimFilter]
@number varchar(20),
@nwId int,
@stId int,
@pageIndex int,
@pageSize int,
@price_min decimal,
@price_max decimal,
@orderBy int,
@numCount int,
@sup_ID int = -1
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets FROM
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF LEN(@number) <> 0
BEGIN
IF CHARINDEX('*', @number) = 0
BEGIN
SET @number = '%' + @number + '%'
END
SET @number = REPLACE(@number, '*', '%')
IF @orderBy = 0
BEGIN
SELECT p.[Number], N.[image], N.[Name], T.[Name] AS SimType, p.Price
FROM (
SELECT [Number], NetWork_ID, SimType_ID, Price, ROW_NUMBER() OVER (ORDER BY Price DESC) AS stt
FROM SIM_CLIENT AS S
WHERE Number LIKE @number
AND ISNULL([NetWork_ID], 0) = ISNULL(NULLIF(@nwId, 0), ISNULL([NetWork_ID], 0))
AND ISNULL([SimType_ID],0) = ISNULL(NULLIF(@stId, 0), ISNULL([SimType_ID], 0))
AND Supplier_ID = ISNULL(NULLIF(@sup_ID, -1), Supplier_ID)
AND LEN([Number]) = ISNULL(NULLIF(@numCount, 0), LEN([Number]))
AND Price >= ISNULL(NULLIF(@price_min, 0), Price)
AND Price <= ISNULL(NULLIF(@price_max, 0), Price)
) AS p
LEFT OUTER JOIN [dbo].[NetWork] AS N
ON p.NetWork_ID = N.ID
LEFT OUTER JOIN [dbo].SimType AS T
ON p.SimType_ID = T.ID
WHERE p.stt > @pageSize * (@pageIndex - 1)
AND p.stt <= @pageSize * @pageIndex
END
ELSE
BEGIN
SELECT TOP 200 [Number], N.[Name], N.[image], T.[Name] AS SimType, p.Price
FROM (
SELECT [Number], NetWork_ID, SimType_ID, Price, ROW_NUMBER() OVER (ORDER BY Price) AS stt
FROM SIM_CLIENT AS S
WHERE Number LIKE @number
AND ISNULL([NetWork_ID], 0) = ISNULL(NULLIF(@nwId, 0), ISNULL([NetWork_ID], 0))
AND ISNULL([SimType_ID],0) = ISNULL(NULLIF(@stId, 0), ISNULL([SimType_ID], 0))
AND Supplier_ID = ISNULL(NULLIF(@sup_ID, -1), Supplier_ID)
AND LEN([Number]) = ISNULL(NULLIF(@numCount, 0), LEN([Number]))
AND Price >= ISNULL(NULLIF(@price_min, 0), Price)
AND Price <= ISNULL(NULLIF(@price_max, 0), Price)
) AS p
LEFT OUTER JOIN [dbo].[NetWork] AS N
ON p.NetWork_ID = N.ID
LEFT OUTER JOIN [dbo].SimType AS T
ON p.SimType_ID = T.ID
WHERE p.stt > @pageSize * (@pageIndex - 1)
AND p.stt <= @pageSize * @pageIndex
END
-- Insert statements for procedure here
END
ELSE
BEGIN
IF @orderBy = 0
BEGIN
SELECT [Number], N.[Name], N.[image], T.[Name] AS SimType, p.Price
FROM (
SELECT [Number], NetWork_ID, SimType_ID, Price, ROW_NUMBER() OVER (ORDER BY Price DESC) AS stt
FROM SIM_CLIENT AS S
WHERE ISNULL([NetWork_ID], 0) = ISNULL(NULLIF(@nwId, 0), ISNULL([NetWork_ID], 0))
AND ISNULL([SimType_ID],0) = ISNULL(NULLIF(@stId, 0), ISNULL([SimType_ID], 0))
AND Supplier_ID = ISNULL(NULLIF(@sup_ID, -1), Supplier_ID)
AND LEN([Number]) = ISNULL(NULLIF(@numCount, 0), LEN([Number]))
AND Price >= ISNULL(NULLIF(@price_min, 0), Price)
AND Price <= ISNULL(NULLIF(@price_max, 0), Price)
) AS p
LEFT OUTER JOIN [dbo].[NetWork] AS N
ON p.NetWork_ID = N.ID
LEFT OUTER JOIN [dbo].SimType AS T
ON p.SimType_ID = T.ID
WHERE p.stt > @pageSize * (@pageIndex - 1)
AND p.stt <= @pageSize * @pageIndex
END
ELSE
BEGIN
SELECT [Number], N.[Name], N.[image], T.[Name] AS SimType, p.Price
FROM (
SELECT [Number], NetWork_ID, SimType_ID, Price, ROW_NUMBER() OVER (ORDER BY Price) AS stt
FROM SIM_CLIENT AS S
WHERE ISNULL([NetWork_ID], 0) = ISNULL(NULLIF(@nwId, 0), ISNULL([NetWork_ID], 0))
AND ISNULL([SimType_ID],0) = ISNULL(NULLIF(@stId, 0), ISNULL([SimType_ID], 0))
AND Supplier_ID = ISNULL(NULLIF(@sup_ID, -1), Supplier_ID)
AND LEN([Number]) = ISNULL(NULLIF(@numCount, 0), LEN([Number]))
AND Price >= ISNULL(NULLIF(@price_min, 0), Price)
AND Price <= ISNULL(NULLIF(@price_max, 0), Price)
) AS p
LEFT OUTER JOIN [dbo].[NetWork] AS N
ON p.NetWork_ID = N.ID
LEFT OUTER JOIN [dbo].SimType AS T
ON p.SimType_ID = T.ID
WHERE p.stt > @pageSize * (@pageIndex - 1)
AND p.stt <=@pageSize * @pageIndex
END
END
END
As I have no data to test with, I've no means to ensure this will help, but my guess is that it probably won't hurt.
Dear Sir,
thanks for your help i tried but not change with my database, I have trouble even in this query:
SELECT [Number], NETWORK_ID SimType_ID, Price, ROW_NUMBER () OVER (ORDER BY Price DESC) AS stt
FROM SIM_CLIENT AS S
Number LIKE WHEREnumber
AND ISNULL ([NETWORK_ID], 0) = ISNULL (NULLIF (nwId, 0), ISNULL ([NETWORK_ID], 0))
AND ISNULL ([SimType_ID], 0) = ISNULL (NULLIF (stId, 0), ISNULL ([SimType_ID], 0))
AND Supplier_ID = ISNULL (NULLIF (sup_ID, -1), Supplier_ID)
AND LEN ([Number]) = ISNULL (NULLIF (numCount, 0), LEN ([Number]))
AND Price> = ISNULL (NULLIF (price_min, 0), Price)
AND Price <= ISNULL (NULLIF (price_max, 0), Price)
when i select with value of paramater i must waiting 27 seconds to get data
August 10, 2015 at 7:27 pm
mrtran0404 (8/10/2015)
Dear Sir,
thanks for your help i tried but not change with my database, I have trouble even in this query:
SELECT [Number], NETWORK_ID SimType_ID, Price, ROW_NUMBER () OVER (ORDER BY Price DESC) AS stt
FROM SIM_CLIENT AS S
Number LIKE WHEREnumber
AND ISNULL ([NETWORK_ID], 0) = ISNULL (NULLIF (nwId, 0), ISNULL ([NETWORK_ID], 0))
AND ISNULL ([SimType_ID], 0) = ISNULL (NULLIF (stId, 0), ISNULL ([SimType_ID], 0))
AND Supplier_ID = ISNULL (NULLIF (sup_ID, -1), Supplier_ID)
AND LEN ([Number]) = ISNULL (NULLIF (numCount, 0), LEN ([Number]))
AND Price> = ISNULL (NULLIF (price_min, 0), Price)
AND Price <= ISNULL (NULLIF (price_max, 0), Price)
when i select with value of paramater i must waiting 27 seconds to get data
The @ signs are missing in the portion of the query you posted. Those are supposed to be the procedure's variables.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 11, 2015 at 1:31 am
ChrisM@Work (8/10/2015)
-- Rewrite your inner query to use SARGable predicates.
Until and unless you change at least one of your predicates to be SARGable, your query will use a full table scan and will always be slow. If you ran my original query and it was still slow then you probably don't have supporting indexes in place. Can you post up the actual execution plan of my query running with a standard set of parameters please? This will allow folks to offer you some indexing suggestions.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 7 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply