July 27, 2020 at 8:53 am
hello all
I wrote this query and my goal is 0 sec for query Result.
Declare
@PrncplCompanyId int = 1,
@FinancialPeriods nvarchar(1000) = '19',
@Stores nvarchar(max)= '0',
@searchQry nvarchar(1000)='',
@Skip int = 1,
@take int = 10,
@fromWh bit =0,
@withParameter bit = 0,
@isWarehouse bit = 0,
@userid int = 266,
@additionalFieldsInSerarch nvarchar(max) = 'TechnicalCode',
@checkInventoryType int = 1,
@showInventoryProduct bit = 1,
@branchId int = 0,
@totalSize int ,
@searchQry2 nvarchar(1000) = '',
@searchQry3 nvarchar(1000) = '',
@searchQry4 nvarchar(1000) = ''
DECLARE @tbl Table(Row int identity(1,1),Id int,Code nvarchar(500),Title nvarchar(4000),TechnicalCode nvarchar(4000),Unit_Id int,Unit_Title nvarchar(255),
ProductStore_Id int,ProductStore_Code nvarchar(500),ProductStore_Title nvarchar(400),ParameterStr nvarchar(4000), CompundType int,
Quantity float,TotalCount int)
Declare @tbl_QbyProduct TABLE (Id int , Quantity float );
Declare @tbl_QbyProductStore TABLE (Id int , StoreId int, Quantity float );
--INSERT INTO @tbl
WITH Main_CTE AS(
SELECT
--CAST(row_number() over (order by Wh_Product.Code,Wh_Store.Code) as int) as [Row] ,
--TotalCount= COUNT(*) OVER(),
Wh_Product.Id,
Wh_Product.Code ,
Wh_Product.Title ,
Wh_Product.TechnicalCode ,
Wh_ProductUnit.Id as Unit_Id,
Wh_ProductUnit.Title as Unit_Title,
Wh_Store.Id as ProductStore_Id,
Wh_Store.Code as ProductStore_Code ,
Wh_Store.Title as ProductStore_Title ,
'' as ParameterStr,
CompundType,
Quantity
FROM Wh_Product
inner join Wh_ProductWh_Store On Wh_ProductWh_Store.Wh_Product_Id = [Wh_Product].Id AND Wh_Product.FD = 0
inner join Wh_Store On Wh_ProductWh_Store.Wh_Store_Id = Wh_Store.Id AND CAST(Wh_Store.StoreType as nvarchar(10)) in (2,4) AND Wh_Store.FD = 0
Inner Join Wh_ProductUnit On Wh_ProductUnit.Id = Wh_Product.UnitId
LEFT Outer Join(Select ProductId,
ProductStore_Id,
ParameterStr,
Sum(Quantity) as Quantity
From (
Select
Wh_Product.Id AS ProductId,
Wh_Product.Code as Product_Code,
Wh_Product.Title as Product_Title,
Wh_Product.TechnicalCode as Product_TechnicalCode,
Wh_Product.CompundType as Product_CompundType,
Wh_ProductUnit.Id as Product_UnitId,
Wh_ProductUnit.Title as ProductUnit_Title,
Wh_Document.StoreId as ProductStore_Id,
Wh_Store.Code as ProductStore_Code,
Wh_Store.Title as ProductStore_Title,
(Case When @withParameter = 1 Then Stuff( (
Select ',' + Convert(nvarchar(100) , Wh_Parameter.Id) + ':' + Replace(Wh_ParameterValue.Value,',','')
From Wh_ParameterValue
inner join Wh_Parameter on Wh_ParameterValue.ParameterId = Wh_Parameter.Id AND IsStockTest = 1
where Wh_DocumentDetail.Id= Wh_ParameterValue.DocumentDetailId AND Wh_Parameter.FD = 0 AND Wh_ParameterValue.FD = 0
Order By ParameterId
For XmL Path('')
),1,1,'')
Else '' END) as ParameterStr,
(Case When Wh_Document.DocType in (1,2,3,8) then (Quantity) when Wh_Document.DocType in (4,5,7) then -1 * Quantity else 0 end) * Rate as Quantity
From [Wh_DocumentDetail]
inner join Wh_Document on Wh_Document.id=[Wh_DocumentDetail].DocumentHeaderId and Wh_Document.FD = 0 and [Wh_DocumentDetail].FD = 0 AND (@branchId = 0 OR Wh_DocumentDetail.CompanyBranchId = @branchId)
INNER JOIN Wh_Product on wh_product.Id = Wh_DocumentDetail.ProductId AND wh_product.fd = 0 AND Wh_Product.PrncplCompanyId = @PrncplCompanyId
INNER JOIN Wh_ProductUnit on Wh_ProductUnit.id = Wh_Product.UnitId AND Wh_ProductUnit.fd = 0
INNER JOIN Wh_Store On Wh_Store.Id = Wh_Document.StoreId AND Wh_Store.FD = 0 and Wh_Store.StoreType in (2,4)
AND ( @Stores is null OR @Stores = '0' OR Wh_Store.Id in (Select * From string_split(@Stores, ',')) )
And (Wh_Store.UsersDeny is Null Or Len(Wh_Store.UsersDeny) = 0 Or @userid = 0 Or @userid Not in ((Select * From string_split(Wh_Store.UsersDeny, ','))))
WHERE ((Wh_Document.DocType in (1,2,3,4,5) and Wh_Document.PrncplFinancialPeriodId in (Select * From string_split(@FinancialPeriods, ','))) Or ( Wh_Document.DocType in (7,8) And Wh_Document.System <> 'Base' And (Wh_Document.IsClose = 0 Or (Wh_DocumentDetail.IsReceptionPartGauranty = 1 And Wh_DocumentDetail.IsCloseReceptionPartGauranty = 0)) ) )
AND Wh_Document.PrncplCompanyId = @PrncplCompanyId
AND (@searchQry is null OR Len(@searchQry) = 0 OR Wh_Product.Code Like N'%' + @searchQry + N'%'OR Wh_Product.Code Like N'%' + @searchQry + N'%'
OR Wh_Product.Title Like N'%' + @searchQry + N'%'
OR ('TechnicalCode' in (Select * From string_split(@additionalFieldsInSerarch, ',')) AND Wh_Product.TechnicalCode Like N'%' + @searchQry + N'%' )
)
AND (@searchQry2 is null OR Len(@searchQry2) = 0 OR Wh_Product.Code Like N'%' + @searchQry2 + N'%'OR Wh_Product.Code Like N'%' + @searchQry2 + N'%'
OR Wh_Product.Title Like N'%' + @searchQry2 + N'%'
OR ('TechnicalCode' in (Select * From string_split(@additionalFieldsInSerarch, ',')) AND Wh_Product.TechnicalCode Like N'%' + @searchQry2 + N'%' )
)
AND (@searchQry3 is null OR Len(@searchQry3) = 0 OR Wh_Product.Code Like N'%' + @searchQry3 + N'%'OR Wh_Product.Code Like N'%' + @searchQry3 + N'%'
OR Wh_Product.Title Like N'%' + @searchQry3 + N'%'
OR ('TechnicalCode' in (Select * From string_split(@additionalFieldsInSerarch, ',')) AND Wh_Product.TechnicalCode Like N'%' + @searchQry3 + N'%' )
)
AND (@searchQry4 is null OR Len(@searchQry4) = 0 OR Wh_Product.Code Like N'%' + @searchQry4 + N'%'OR Wh_Product.Code Like N'%' + @searchQry4 + N'%'
OR Wh_Product.Title Like N'%' + @searchQry4 + N'%'
OR ('TechnicalCode' in (Select * From string_split(@additionalFieldsInSerarch, ',')) AND Wh_Product.TechnicalCode Like N'%' + @searchQry4 + N'%' )
)
) as t
GROUP BY ProductId, Product_code, Product_Title, Product_TechnicalCode,Product_UnitId,
ProductUnit_Title, ProductStore_Id , ProductStore_Code, ProductStore_Title,
ParameterStr,Product_CompundType
) as tQty On tQty.ProductId = Wh_Product.Id And tQty.ProductStore_Id = Wh_store.Id
WHERE(@Stores is null Or @Stores = '0' OR LEN(@Stores) = 0 OR Wh_Store.Id in (Select * From string_split(@Stores,',')))
And (Wh_Store.UsersDeny is Null Or Len(Wh_Store.UsersDeny) = 0 Or @userid = 0 Or @userid Not in ((Select * From string_split(Wh_Store.UsersDeny, ','))))
AND (@searchQry is null OR Len(@searchQry) = 0 OR Wh_Product.Code Like N'%' + @searchQry + N'%'
OR Wh_Product.Title Like N'%' + @searchQry + N'%'
OR ('TechnicalCode' in (Select * From string_split(@additionalFieldsInSerarch, ',')) AND Wh_Product.TechnicalCode Like N'%' + @searchQry + N'%' )
)
AND (@searchQry2 is null OR Len(@searchQry2) = 0 OR Wh_Product.Code Like N'%' + @searchQry2 + N'%'OR Wh_Product.Code Like N'%' + @searchQry2 + N'%'
OR Wh_Product.Title Like N'%' + @searchQry2 + N'%'
OR ('TechnicalCode' in (Select * From string_split(@additionalFieldsInSerarch, ',')) AND Wh_Product.TechnicalCode Like N'%' + @searchQry2 + N'%' )
)
AND (@searchQry3 is null OR Len(@searchQry3) = 0 OR Wh_Product.Code Like N'%' + @searchQry3 + N'%'OR Wh_Product.Code Like N'%' + @searchQry3 + N'%'
OR Wh_Product.Title Like N'%' + @searchQry3 + N'%'
OR ('TechnicalCode' in (Select * From string_split(@additionalFieldsInSerarch, ',')) AND Wh_Product.TechnicalCode Like N'%' + @searchQry3 + N'%' )
)
AND (@searchQry4 is null OR Len(@searchQry4) = 0 OR Wh_Product.Code Like N'%' + @searchQry4 + N'%'OR Wh_Product.Code Like N'%' + @searchQry4 + N'%'
OR Wh_Product.Title Like N'%' + @searchQry4 + N'%'
OR ('TechnicalCode' in (Select * From string_split(@additionalFieldsInSerarch, ',')) AND Wh_Product.TechnicalCode Like N'%' + @searchQry4 + N'%' )
)
AND Wh_Product.PrncplCompanyId = @PrncplCompanyId AND Wh_Store.PrncplCompanyId = @PrncplCompanyId
And (@showInventoryProduct = 0 Or tQty.Quantity > 0)
)
,Count_CTE AS (
SELECT count(*) AS [TotalCount]
FROM Main_CTE
)
INSERT INTO @tbl
SELECT *
FROM Main_CTE
cross join Count_CTE
Order By Main_CTE.Code,Main_CTE.ProductStore_Code
OFFSET (@skip-1)*@take ROWS -- skip 10 rows
FETCH NEXT @take ROWS ONLY;
select * from @tbl
This Query get result in 0 sec without total count but 3 sec with total count.
how can i get total count in fastest way?
July 27, 2020 at 4:57 pm
This may or may not help, but if you turn Main_CTE into a table (temp table, table variable, physical table... not sure which will be the best for your use case), tossing an index on it and having statistics on it will make your count faster (or at least should). But it comes at the performance hit of writing things out to disk.
Since you are dumping things into a table variable anyways, I expect that the temp table/table variable for main_cte should be quick.
One of my first steps to figure out performance issues is to get an execution plan. I would grab an execution plan without the count and with the count.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 29, 2020 at 12:20 pm
Also, in addition to Brian's advice, take a look at this snippet of code that is a JOIN condition:
CAST(Wh_Store.StoreType as nvarchar(10)) in (2,4)
Why convert a column to the nvarchar data type only to then compare the value to integers? That could easily be a part of slowing things down, and the more rows for that join, the worse it would get. What is the data type of Wh_Store.StoreType ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 2, 2020 at 5:14 am
thanks for your advice.I get my result in 2 sec with index on tables.but I just need to decrease query time to 0 sec.
August 4, 2020 at 4:26 pm
You want this to be 0 seconds... a fun challenge, but may not be possible.
How often does this data change? Are you OK with "snapshot" data rather than realtime data? If so - set the query to fire on a schedule to update a table on disk. Then, when you need to run your above query, instead of having that big query, you have a simple SELECT from a single table.
If you need real-time data and NOT snapshot data, then you are going to need to find your bottleneck. Which portion of the query is the "slow" part. Grab an execution plan so you can see everything that is happening and dig into that. BUT also keep in mind that the slowness MIGHT not be on the SQL side. It could be Disk I/O, RAM, CPU, Network I/O, etc. It might be that the 2 seconds is as quick as you can get it with the hardware at your disposal.
If you can simplify/remove some of the WHERE's you should get a performance boost (not sure if you can); getting the data immediately rather than writing it to the temp table @tbl should also give a bit of a performance boost as you don't need to write to disk. That being said, you are writing and reading a small number of rows, so I don't expect too much of a performance boost here.
Another thought - is it the COUNT that is being slow or the CROSS JOIN that is being slow? My thoughts here are if the query completes in 0 seconds, removing the CROSS JOIN and doing a UNION on the count CTE (single row, so should be pretty quick), then changing your last SELECT * to select all columns except instead of TotalCount, select the MAX of TotalCount in a windowing function with something like PARTITION BY 1, you MAY get a performance boost. That is presuming it is the CROSS JOIN that is being your performance hit which may or may not be the case.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply