Click here to Skip to main content
15,885,278 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi,

I need to count the record from the cte query,

I did this way,

SQL
with cte as (

select distinct cust.Name as CompanyName,ROW_NUMBER() OVER(order by (InvoiceDate) Desc)  as RowNumber,DATEDIFF(DAY  ,inv.InvoicedueDate ,getdate()) DaysTillDate ,                              
inv.InvoiceId,dbo.fn_GetOrderFromInv(inv.InvoiceId) as OrderNumber ,inv.InvoiceDate, (inv.Prefix + '' + Convert(varchar,inv.InvoiceNo)) as InvoiceNo  ,Del.DeliveryNo,Hcomp.Name as HandlingCompany,                              
CONVERT(decimal(18,3), inv.InvoiceValue) as Base_Invoiceamount,CONVERT(decimal(18,2), inv.Currency_InvoiceValue ) as Cust_Invoiceamount,inv.InvoiceDueDate,             
Isnull(((inv.InvoiceValue) - (select SUM(Amount) from genPaymentRegistration where InvoiceId = inv.InvoiceId )),inv.InvoiceValue) as Base_Outstanding,
Convert(decimal(12,2), Isnull(((inv.Currency_InvoiceValue) - (select (SUM(Currency_Amount) ) from genPaymentRegistration where InvoiceId = inv.InvoiceId )),(inv.Currency_InvoiceValue))) as Cust_Outstanding,
inv.InvoiceTypeId,        
dbo.fn_GetinvoiceCNType(inv.InvoiceTypeId,493 ,inv.InvoiceId,inv.InvoiceValue) Note        
from genInvoices inv                              
left join genInvoiceOrders invord on invord.InvoiceId =inv.InvoiceId                        
left join genOrders ord on ord.OrderId =invord.OrderId                               
left join genCompanies cust on ord.CompanyId = cust.CompanyId                              
left join genDelivery Del on Del.DeliveryId = inv.DeliveryId                              
left join genCompanies Hcomp on Hcomp.CompanyID = inv.HandCompanyId  
where ( inv.StatusId=307 ) and ( inv.InvoiceTypeId = 3 or inv.InvoiceTypeId = 1 ) and ((isnull(inv.IsInvoiceDeliver,0)=1 and isnull(inv.IsEinvoice,0)=1) or (isnull(inv.IsDeliverbyPost,0)=1) or (isnull(inv.IsEinvoice,0)=0 and isnull(inv.IsDeliverbyPost,0)=0) )  and inv.CustCompanyId = 45084 and  1=1 and  1=1)
, cte2 as (select count(*) as cnt from cte)
select  RowNumber,cnt, CompanyName,DaysTillDate,InvoiceId,OrderNumber, dbo.fn_globaldateformat(InvoiceDate,493) as InvoiceDate,InvoiceDate as InvoiceDatesort,InvoiceNo,DeliveryNo,
HandlingCompany,Base_Invoiceamount,Cust_Invoiceamount,dbo.fn_globaldateformat(InvoiceDueDate,493) as InvoiceDueDate,InvoiceDueDate as InvoiceDueDatesort,
Base_Outstanding,Cust_Outstanding,InvoiceTypeId,Note from cte,cte2 where RowNumber between  1 and 20


this query takes 700ms to execute,

but when I remove another cte(i.e. removing count()) than it takes only 250ms to exeute,

after removing Count the query is,

SQL
with cte as (

select distinct cust.Name as CompanyName,ROW_NUMBER() OVER(order by (InvoiceDate) Desc)  as RowNumber,DATEDIFF(DAY  ,inv.InvoicedueDate ,getdate()) DaysTillDate ,                              
inv.InvoiceId,dbo.fn_GetOrderFromInv(inv.InvoiceId) as OrderNumber ,inv.InvoiceDate, (inv.Prefix + '' + Convert(varchar,inv.InvoiceNo)) as InvoiceNo  ,Del.DeliveryNo,Hcomp.Name as HandlingCompany,                              
CONVERT(decimal(18,3), inv.InvoiceValue) as Base_Invoiceamount,CONVERT(decimal(18,2), inv.Currency_InvoiceValue ) as Cust_Invoiceamount,inv.InvoiceDueDate,             
Isnull(((inv.InvoiceValue) - (select SUM(Amount) from genPaymentRegistration where InvoiceId = inv.InvoiceId )),inv.InvoiceValue) as Base_Outstanding,
Convert(decimal(12,2), Isnull(((inv.Currency_InvoiceValue) - (select (SUM(Currency_Amount) ) from genPaymentRegistration where InvoiceId = inv.InvoiceId )),(inv.Currency_InvoiceValue))) as Cust_Outstanding,
inv.InvoiceTypeId,        
dbo.fn_GetinvoiceCNType(inv.InvoiceTypeId,493 ,inv.InvoiceId,inv.InvoiceValue) Note        
from genInvoices inv                              
left join genInvoiceOrders invord on invord.InvoiceId =inv.InvoiceId                        
left join genOrders ord on ord.OrderId =invord.OrderId                               
left join genCompanies cust on ord.CompanyId = cust.CompanyId                              
left join genDelivery Del on Del.DeliveryId = inv.DeliveryId                              
left join genCompanies Hcomp on Hcomp.CompanyID = inv.HandCompanyId  
where ( inv.StatusId=307 ) and ( inv.InvoiceTypeId = 3 or inv.InvoiceTypeId = 1 ) and ((isnull(inv.IsInvoiceDeliver,0)=1 and isnull(inv.IsEinvoice,0)=1) or (isnull(inv.IsDeliverbyPost,0)=1) or (isnull(inv.IsEinvoice,0)=0 and isnull(inv.IsDeliverbyPost,0)=0) )  and inv.CustCompanyId = 45084 and  1=1 and  1=1)

select  RowNumber, CompanyName,DaysTillDate,InvoiceId,OrderNumber, dbo.fn_globaldateformat(InvoiceDate,493) as InvoiceDate,InvoiceDate as InvoiceDatesort,InvoiceNo,DeliveryNo,
HandlingCompany,Base_Invoiceamount,Cust_Invoiceamount,dbo.fn_globaldateformat(InvoiceDueDate,493) as InvoiceDueDate,InvoiceDueDate as InvoiceDueDatesort,
Base_Outstanding,Cust_Outstanding,InvoiceTypeId,Note from cte where RowNumber between  1 and 20


My only question is that how can I count record from cte query?

Thanks.
Posted
Updated 20-Jan-13 2:56am
v4

1 solution

If you have all indexes in place, and the query is as complicated as it seems to be, 700ms is not so much, if you don't want to run it every 500ms :)

As I see, this is a generated query. I suggest you try to optimize it, by not adding extra constraints like 1=1, and moving all conversions to the most outher level. Do you really need those ISNULL calls?

If you can afford to store the result in a temporary table for a while you should consider this approach too.
 
Share this answer
 
Comments
ExpertITM 20-Jan-13 10:28am    
hi, Zoltan

My question is that when I run this query without the count() it takes about 250ms but when I select count() than it takes about 700ms

??

Thanks.
Zoltán Zörgő 20-Jan-13 11:45am    
And? Your query needs manual optimizations. The built-in optimizer is not able to do it for you since it is too complicated.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900