please help me: select is verry slow from table 5 milion record

Question

Post reply

please help me: select is verry slow from table 5 milion record

mrtran0404

Old Hand

Points: 360
More actions
August 9, 2015 at 8:17 pm

#310751

Dear all,
i having isue is when i select data form my table took more than 10 seconds to get result,
there are solutions that can help me get the data from my data table as quickly as possible not?
please help me?
this my my struck of table:
CREATE TABLE [dbo].[SIM](
[ID] [int] IDENTITY(1,1) NOT NULL,[Number] [varchar](50) NULL,[NetWork_ID] [int] NULL,
[SimType_ID] [int] NULL,[Supplier_ID] [int] NULL,[Price] [money] NULL,
[Status] [int] NULL,[isActive] [bit] NULL,[isDeleted] [bit] NULL,
[CreateBy] [varchar](50) NULL,[CreateDate] [datetime] NULL,
[UpdateBy] [varchar](50) NULL,[LastUpdate] [date] NULL,
[TrangThaiSim] [nvarchar](200) NULL,[CamKet] [nvarchar](100) NULL,[Price_Sup] [money] NULL,
CONSTRAINT [PK_dbo.SIM] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
this is my query string:
select
[Number],
NetWork_ID,
SimType_ID,
Price,
row_number() over (order by Price desc) stt
from SIM_CLIENT S
where Number like '%%'
and ISNULL([NetWork_ID],0) = case when 0 = 0 then ISNULL([NetWork_ID],0) else 0 end
and ISNULL([SimType_ID],0) = case when 0 = 0 then ISNULL([SimType_ID],0) else 0 end
and (Supplier_ID = -1 OR -1 = -1)
and LEN([Number]) = case when 0 = 0 then LEN([Number]) else 0 end
and Price >= case when 0 = 0 then Price else 0 end
and Price <= case when 0 = 0 then Price else 0 end
i was create nonecluster index for all columne on this table
please help help
thank all and have nice day

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

mrtran0404 Old Hand Points: 360 More actions · Answer 1

anny idea for this propblem? please help me!

Thanks for you time!

andreas.kreuzberg SSCertifiable Points: 6206 More actions · Answer 2

Hi,

had you tried to count the IO, when the query runs:

https://msdn.microsoft.com/en-us/library/ms184361.aspx

And what about the execution plan, can you see something interesting inside?

Regards,

Andreas

mrtran0404 Old Hand Points: 360 More actions · Answer 3

Dear sir,

thank for you answer, yes i used SET STATISICS IO but time select no change.

this is my sqlplan affter run select:

<?xml version="1.0" encoding="utf-16"?>

<BatchSequence>

<Batch>

<Statements>

<StmtSimple StatementCompId="1" StatementEstRows="1490880" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="10.2357" StatementText="select [Number], NetWork_ID, SimType_ID, Price, row_number() over (order by Price desc) stt from SIM_CLIENT S where Number like '%%' and ISNULL([NetWork_ID],0) = case when 0 = 0 then ISNULL([NetWork_ID],0) else 0 end and ISNULL([SimType_ID],0) = case when 0 = 0 then ISNULL([SimType_ID],0) else 0 end and (Supplier_ID = -1 OR -1 = -1) and LEN([Number]) = case when 0 = 0 then LEN([Number]) else 0 end and Price >= case when 0 = 0 then Price else 0 end and Price <= case when 0 = 0 then Price else 0 end" StatementType="SELECT" QueryHash="0x511D947EA0B0B7FF" QueryPlanHash="0x950E57A682A196A2">

<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />

<QueryPlan CachedPlanSize="32" CompileTime="2637" CompileCPU="1356" CompileMemory="544">

<OutputList>

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="Number" />

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="NetWork_ID" />

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="SimType_ID" />

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="Price" />

<ColumnReference Column="Expr1002" />

</OutputList>

<SequenceProject>

<DefinedValues>

<DefinedValue>

<ColumnReference Column="Expr1002" />

<ScalarOperator ScalarString="row_number">

<Sequence FunctionName="row_number" />

</ScalarOperator>

</DefinedValue>

</DefinedValues>

<OutputList>

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="Number" />

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="NetWork_ID" />

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="SimType_ID" />

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="Price" />

<ColumnReference Column="Segment1006" />

</OutputList>

<Segment>

<GroupBy />

<SegmentColumn>

<ColumnReference Column="Segment1006" />

</SegmentColumn>

<OutputList>

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="Number" />

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="NetWork_ID" />

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="SimType_ID" />

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="Price" />

</OutputList>

<Parallelism>

<OrderBy>

<OrderByColumn Ascending="false">

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="Price" />

</OrderByColumn>

</OrderBy>

<OutputList>

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="Number" />

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="NetWork_ID" />

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="SimType_ID" />

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="Price" />

</OutputList>

<MemoryFractions Input="1" Output="1" />

<Sort Distinct="false">

<OrderBy>

<OrderByColumn Ascending="false">

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="Price" />

</OrderByColumn>

</OrderBy>

<OutputList>

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="Number" />

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="NetWork_ID" />

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="SimType_ID" />

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="Price" />

</OutputList>

<IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">

<DefinedValues>

<DefinedValue>

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="Number" />

</DefinedValue>

<DefinedValue>

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="NetWork_ID" />

</DefinedValue>

<DefinedValue>

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="SimType_ID" />

</DefinedValue>

<DefinedValue>

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="Price" />

</DefinedValue>

</DefinedValues>

<Object Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Index="[_dta_index_SIM_CLIENT_10_421576540__K5_1_2_3]" Alias="" IndexKind="NonClustered" />

<Predicate>

<ScalarOperator ScalarString="[sv_sodep].[dbo].[SIM_CLIENT].[Price] as .[Price]>=[sv_sodep].[dbo].[SIM_CLIENT].[Price] as .[Price] AND [sv_sodep].[dbo].[SIM_CLIENT].[Price] as .[Price]<=[sv_sodep].[dbo].[SIM_CLIENT].[Price] as .[Price] AND len([sv_sodep].[dbo].[SIM_CLIENT].[Number] as .[Number])=len([sv_sodep].[dbo].[SIM_CLIENT].[Number] as .[Number]) AND [sv_sodep].[dbo].[SIM_CLIENT].[Number] as .[Number] like N'%%' AND isnull([sv_sodep].[dbo].[SIM_CLIENT].[NetWork_ID] as .[NetWork_ID],(0))=isnull([sv_sodep].[dbo].[SIM_CLIENT].[NetWork_ID] as .[NetWork_ID],(0)) AND isnull([sv_sodep].[dbo].[SIM_CLIENT].[SimType_ID] as .[SimType_ID],(0))=isnull([sv_sodep].[dbo].[SIM_CLIENT].[SimType_ID] as .[SimType_ID],(0))">

<Logical Operation="AND">

<ScalarOperator>

<Compare CompareOp="GE">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="Price" />

</Identifier>

</ScalarOperator>

<ScalarOperator>

<Identifier>

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="Price" />

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

<ScalarOperator>

<Compare CompareOp="LE">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="Price" />

</Identifier>

</ScalarOperator>

<ScalarOperator>

<Identifier>

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="Price" />

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

<ScalarOperator>

<Compare CompareOp="EQ">

<ScalarOperator>

<Intrinsic FunctionName="len">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="Number" />

</Identifier>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

<ScalarOperator>

<Intrinsic FunctionName="len">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="Number" />

</Identifier>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</Compare>

</ScalarOperator>

<ScalarOperator>

<Intrinsic FunctionName="like">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="Number" />

</Identifier>

</ScalarOperator>

<ScalarOperator>

<Const ConstValue="N'%%'" />

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

<ScalarOperator>

<Compare CompareOp="EQ">

<ScalarOperator>

<Intrinsic FunctionName="isnull">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="NetWork_ID" />

</Identifier>

</ScalarOperator>

<ScalarOperator>

<Const ConstValue="(0)" />

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

<ScalarOperator>

<Intrinsic FunctionName="isnull">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="NetWork_ID" />

</Identifier>

</ScalarOperator>

<ScalarOperator>

<Const ConstValue="(0)" />

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</Compare>

</ScalarOperator>

<ScalarOperator>

<Compare CompareOp="EQ">

<ScalarOperator>

<Intrinsic FunctionName="isnull">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="SimType_ID" />

</Identifier>

</ScalarOperator>

<ScalarOperator>

<Const ConstValue="(0)" />

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

<ScalarOperator>

<Intrinsic FunctionName="isnull">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[sv_sodep]" Schema="[dbo]" Table="[SIM_CLIENT]" Alias="" Column="SimType_ID" />

</Identifier>

</ScalarOperator>

<ScalarOperator>

<Const ConstValue="(0)" />

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</Sort>

</RelOp>

</Parallelism>

</RelOp>

</Segment>

</RelOp>

</SequenceProject>

</RelOp>

</QueryPlan>

</StmtSimple>

</Statements>

</Batch>

</BatchSequence>

</ShowPlanXML>

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

mrtran0404 (8/9/2015)
Dear all,
i having isue is when i select data form my table took more than 10 seconds to get result,
there are solutions that can help me get the data from my data table as quickly as possible not?
please help me?
this my my struck of table:
CREATE TABLE [dbo].[SIM](
[ID] [int] IDENTITY(1,1) NOT NULL,[Number] [varchar](50) NULL,[NetWork_ID] [int] NULL,
[SimType_ID] [int] NULL,[Supplier_ID] [int] NULL,[Price] [money] NULL,
[Status] [int] NULL,[isActive] [bit] NULL,[isDeleted] [bit] NULL,
[CreateBy] [varchar](50) NULL,[CreateDate] [datetime] NULL,
[UpdateBy] [varchar](50) NULL,[LastUpdate] [date] NULL,
[TrangThaiSim] [nvarchar](200) NULL,[CamKet] [nvarchar](100) NULL,[Price_Sup] [money] NULL,
CONSTRAINT [PK_dbo.SIM] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
this is my query string:
select
[Number],
NetWork_ID,
SimType_ID,
Price,
row_number() over (order by Price desc) stt
from SIM_CLIENT S
where Number like '%%'
and ISNULL([NetWork_ID],0) = case when 0 = 0 then ISNULL([NetWork_ID],0) else 0 end
and ISNULL([SimType_ID],0) = case when 0 = 0 then ISNULL([SimType_ID],0) else 0 end
and (Supplier_ID = -1 OR -1 = -1)
and LEN([Number]) = case when 0 = 0 then LEN([Number]) else 0 end
and Price >= case when 0 = 0 then Price else 0 end
and Price <= case when 0 = 0 then Price else 0 end
i was create nonecluster index for all columne on this table
please help help
thank all and have nice day

Remove all of the rubbish from your WHERE clause. Most of it doesn't do anything at all, some of it excludes any rows from being returned.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

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 end

This has to be a prank.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

mrtran0404 Old Hand Points: 360 More actions · Answer 7

no i select folow value of variable when i call storeprocedure and then it exec this command to return result to me 😎

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

mrtran0404 (8/10/2015)
no i select folow value of variable when i call storeprocedure and then it exec this command to return result to me 😎

The query you have posted is nonsense, as John pointed out it returns all rows so you might as well omit the WHERE clause altogether. However, the query you posted may not be the query which is executed. How are you running the query? Can you post more of the stored procedure?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

mrtran0404 Old Hand Points: 360 More actions · Answer 9

ChrisM@Work (8/10/2015)
mrtran0404 (8/10/2015)
no i select folow value of variable when i call storeprocedure and then it exec this command to return result to me 😎
The query you have posted is nonsense, as John pointed out it returns all rows so you might as well omit the WHERE clause altogether. However, the query you posted may not be the query which is executed. How are you running the query? Can you post more of the stored procedure?

yes sir:

this is my storeprocedure:

USE [sv_sodep]

GO

/****** 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

[Number],

(SELECT [image] FROM [dbo].[NetWork] WHERE ID=p.NetWork_ID)[image],

(SELECT [Name] FROM [dbo].[NetWork] WHERE ID=p.NetWork_ID)[Name],

(SELECT [Name] FROM [dbo].SimType WHERE ID=p.SimType_ID)[SimType],

Price

from (

select

[Number],

NetWork_ID,

SimType_ID,

Price,

row_number() over (order by Price desc) stt

from SIM_CLIENT S

where Number like @number

and ISNULL([NetWork_ID],0) = case when @nwId = 0 then ISNULL([NetWork_ID],0) else @nwId end

and ISNULL([SimType_ID],0) = case when @stId = 0 then ISNULL([SimType_ID],0) else @stId end

and (Supplier_ID = @sup_ID OR @sup_ID = -1)

and LEN([Number]) = case when @numCount = 0 then LEN([Number]) else @numCount end

and Price >= case when @price_min = 0 then Price else @price_min end

and Price <= case when @price_max = 0 then Price else @price_max end

) p

where p.stt > @pageSize*(@pageIndex-1) and p.stt <=@pageSize*(@pageIndex)

end

else

begin

select top 200

[Number],

(SELECT [image] FROM [dbo].[NetWork] WHERE ID=p.NetWork_ID)[Name],

(SELECT [Name] FROM [dbo].[NetWork] WHERE ID=p.NetWork_ID)[image],

(SELECT [Name] FROM [dbo].SimType WHERE ID=p.SimType_ID)[SimType],

Price

from (

select

[Number],

NetWork_ID,

SimType_ID,

Price,

row_number() over (order by Price) stt

from SIM_CLIENT S

where Number like @number

and ISNULL([NetWork_ID],0) = case when @nwId = 0 then ISNULL([NetWork_ID],0) else @nwId end

and ISNULL([SimType_ID],0) = case when @stId = 0 then ISNULL([SimType_ID],0) else @stId end

and (Supplier_ID = @sup_ID OR @sup_ID = -1)

and LEN([Number]) = case when @numCount = 0 then LEN([Number]) else @numCount end

and Price >= case when @price_min = 0 then Price else @price_min end

and Price <= case when @price_max = 0 then Price else @price_max end

) p

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],

(SELECT [Name] FROM [dbo].[NetWork] WHERE ID=p.NetWork_ID)[Name],

(SELECT [image] FROM [dbo].[NetWork] WHERE ID=p.NetWork_ID)[image],

(SELECT [Name] FROM [dbo].SimType WHERE ID=p.SimType_ID)[SimType],

Price

from (

select

[Number],

NetWork_ID,

SimType_ID,

Price,

row_number() over (order by Price desc) stt

from SIM_CLIENT S

where ISNULL([NetWork_ID],0) = case when @nwId = 0 then ISNULL([NetWork_ID],0) else @nwId end

and ISNULL([SimType_ID],0) = case when @stId = 0 then ISNULL([SimType_ID],0) else @stId end

and (Supplier_ID = @sup_ID OR @sup_ID = -1)

and LEN([Number]) = case when @numCount = 0 then LEN([Number]) else @numCount end

and Price >= case when @price_min = 0 then Price else @price_min end

and Price <= case when @price_max = 0 then Price else @price_max end

) p

where p.stt > @pageSize*(@pageIndex-1) and p.stt <=@pageSize*(@pageIndex)

end

else

begin

select

[Number],

(SELECT [image] FROM [dbo].[NetWork] WHERE ID=p.NetWork_ID)[image],

(SELECT [Name] FROM [dbo].[NetWork] WHERE ID=p.NetWork_ID)[Name],

(SELECT [Name] FROM [dbo].SimType WHERE ID=p.SimType_ID)[SimType],

Price

from (

select

[Number],

NetWork_ID,

SimType_ID,

Price,

row_number() over (order by Price) stt

from SIM_CLIENT S

where ISNULL([NetWork_ID],0) = case when @nwId = 0 then ISNULL([NetWork_ID],0) else @nwId end

and ISNULL([SimType_ID],0) = case when @stId = 0 then ISNULL([SimType_ID],0) else @stId end

and (Supplier_ID = @sup_ID OR @sup_ID = -1)

and LEN([Number]) = case when @numCount = 0 then LEN([Number]) else @numCount end

and Price >= case when @price_min = 0 then Price else @price_min end

and Price <= case when @price_max = 0 then Price else @price_max end

) p

where p.stt > @pageSize*(@pageIndex-1) and p.stt <=@pageSize*(@pageIndex)

end

END

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 10

The issue is still that, with the set of parameters you've passed in, you're selecting every row from the table. What you originally posted, though, was only the subquery - have you tried optimising the whole thing? It may be that the outer query eliminates many of the rows.

I'm afraid I lost the will to live after looking at the stored procedure definition for five minutes - the lack of formatting makes it difficult to understand. You should, at the very least, indent your BEGIN...END blocks. There's a TOP clause without an ORDER BY - what's the purpose of that. And I would advise you to get rid of the correlated subqueries and use joins instead.

John

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

-- Rewrite your inner query to use SARGable predicates.

-- Better still, consider a rewrite: build the query up as a string including only

-- the search criteria which will be used. "One size fits all" or "catch-all" queries

-- generate an execution plan which may work well with one set of parameters and poorly

-- with another.

SELECT

s.[Number],

s.Price,

[image] = n.[image],

[Name] = n.[Name],

[SimType] = st.[Name],

stt = ROW_NUMBER() OVER (ORDER BY s.Price DESC)

FROM SIM_CLIENT s

LEFT JOIN [dbo].[NetWork] n

ON n.ID = s.NetWork_ID

LEFT JOIN [dbo].SimType st

ON st.ID = s.SimType_ID

WHERE 1 = 1

-- LIKE '%something%' cannot use an index. Consider changing this to LIKE 'something%', or

-- ensuring that this search predicate cannot be used alone

AND s.Number LIKE @number

--and ISNULL([NetWork_ID],0) = case when @nwId = 0 then ISNULL([NetWork_ID],0) else @nwId end

AND (@nwId = 0 OR s.NetWork_ID = @nwId)

--and ISNULL([SimType_ID],0) = case when @stId = 0 then ISNULL([SimType_ID],0) else @stId end

AND (@stId = 0 OR s.SimType_ID = @stId)

AND (@sup_ID = -1 OR s.Supplier_ID = @sup_ID)

--and LEN([Number]) = case when @numCount = 0 then LEN([Number]) else @numCount end

AND (@numCount = 0 OR LEN(s.Number) = @numCount)

--and Price >= case when @price_min = 0 then Price else @price_min end

AND (@price_min = 0 OR s.Price >= @price_min)

--and Price <= case when @price_max = 0 then Price else @price_max end

AND (@price_max = 0 OR s.Price <= @price_max)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Sean Lange SSC Guru Points: 286609 More actions · Answer 12

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 end
This 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:

_______________________________________________________________

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/

mrtran0404 Old Hand Points: 360 More actions · Answer 13

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 end
This 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

Sean Lange SSC Guru Points: 286609 More actions · Answer 14

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 end
This 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?

_______________________________________________________________

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/