Just a solution - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Just a solution

Author

Topic

nirene
Yak Posting Veteran

98 Posts

Posted - 2007-09-27 : 01:25:57

Hello All,
I have a transaction table that holds nearly a million records and my master with 60000 records,I want to fetch details based on two dates.

I use Union operator to query the info from a view,is this right or fetch the result into a temporary table and fetch the result from temporary table using the same Union operator.

Which is the better way to fetch details faster.

Nirene

My SP is pasted below just advice me to execute this more efficiently,cos this SP is called thru a Web application.

CREATE PROCEDURE GL @SDt Varchar(10),@EDt Varchar(10),@Loccode Char(5),@OP Char(4)
AS

Declare @OpBalQry NVarchar(1000),@TrnQry NVarchar(2000),@MainSQry NVarchar(500),@MainEQry NVarchar(500)
Declare @Cocode Char(5)
Declare @CashGL Char(6),@Gldesc Varchar(50)

IF EXISTS (Select 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='#Gltmp')
Begin
Drop Table #Gltmp
End

Select @Cocode=Cocode from Location Where Loccode=@Loccode
Select @CashGL=Glcode,@Gldesc=Gldesc from Glmast Where Cocode=@Cocode and Subgroupcode='CASH'

Set @OpBalQry='Select A.Glcode,'''' as Trtype,''' + @SDt + ''' as Refdt,'' Opening Balance'' as Refno,
Max(B.Gldesc) as Narration,Case When (Sum(A.Deb)-Sum(A.Cre))<0 Then (-1*(Sum(A.Deb)-Sum(A.Cre))) Else 0 End as Dr,
Case When (Sum(A.Deb)-Sum(A.Cre))>=0 Then (Sum(A.Deb)-Sum(A.Cre)) Else 0 End as Cr from
(
Select Glcode,
(Case When Drcrflag=''D'' Then Opbal Else 0 End) as Deb,
(Case When Drcrflag=''C'' Then Opbal Else 0 End) as Cre from Obdata
Union
Select Glcode,
(Case When Drcrflag=''D'' Then Tramt Else 0 End) as Deb,
(Case When Drcrflag=''C'' Then Tramt Else 0 End) as Cre from Trans_Journal
Where Refdt <Convert(Datetime,''' + @SDt + ''',103)
) A,Glmast B Where B.Cocode=''' + @Cocode + ''' and A.Glcode=B.Glcode Group By A.Glcode
Union
'
Set @TrnQry='SELECT T.Glcode,T.Trtype,Convert(Varchar,Max(T.Refdt),103) as Refdt , '''' AS Refno,
(Rtrim(Ltrim(Max(G.Gldesc))) + '' -- From Sub-Ledger'') AS Narration,
SUM(CASE WHEN T.Drcrflag = ''D'' THEN T.tramt ELSE 0 END) AS Dr,
SUM(CASE WHEN T.Drcrflag = ''C'' THEN T.tramt ELSE 0 END) AS Cr
FROM Trans_Journal T,Glmast G
WHERE T.Glcode=G.Glcode and G.Cocode=''' + @Cocode + ''' and (T.Slcode IS NOT NULL AND Len(Rtrim(Ltrim(T.Slcode)))>0) and
(T.Refdt>=Convert(Datetime,''' + @SDt + ''',103) and T.Refdt <=Convert(Datetime,''' + @EDt + ''',103)
and T.Loccode= ''' + @Loccode + ''' and (G.Subgroupcode<>''BANK'' or G.Subgroupcode<>''CASH''))
GROUP BY T.Glcode,T.Trtype
UNION
SELECT A.Glcode,'''' as Trtype,Convert(Varchar,A.Refdt,103) as Refdt, A.Refno, B.Gldesc AS Narration,
CASE WHEN A.Drcrflag = ''D'' THEN A.Tramt ELSE 0 END AS Dr,
CASE WHEN A.Drcrflag = ''C'' THEN A.Tramt ELSE 0 END AS Cr
FROM Trans_Journal, Glmast B
WHERE (A.Glcode = B.Glcode and B.Cocode=''' + @Cocode + ''') and
(A.Slcode IS NULL OR Len(Rtrim(Ltrim(A.Slcode)))=0) and
(A.Refdt>=Convert(Datetime,''' + @SDt + ''',103) and A.Refdt<=Convert(Datetime,''' + @EDt + ''',103))
and A.Loccode=''' + @Loccode + ''' and (B.Subgroupcode<>''BANK'' or B.Subgroupcode<>''CASH'')
Union
Select Glcode,Trtype,Convert(Varchar,Max(Refdt),103) as Refdt,'' '' as Refno,Max(Narration) as Narration,Sum(Dr) as Dr,Sum(Cr) as Cr from
(Select A.Glcode,A.Trtype,A.Refdt,B.Gldesc as Narration,
CASE WHEN A.Drcrflag =''D'' THEN A.Tramt ELSE 0 END AS Dr, CASE WHEN A.Drcrflag =''C'' THEN A.Tramt ELSE 0 END AS Cr
from Trans_Journal,Glmast B
WHERE
(A.Glcode=B.Glcode and B.Cocode=''' + @Cocode + ''' and A.Refdt>=Convert(Datetime,''' + @SDt + ''',103)
and A.Refdt<=Convert(Datetime,''' + @EDt + ''' ,103)
and A.Loccode=''' + @Loccode + ''') and (B.Subgroupcode=''BANK'' or B.Subgroupcode=''CASH'')
Union
Select ''' + @CashGL + ''' as Glcode,Trtype,Refdt,''' + @Gldesc + ''' as Narration,
CASE WHEN Drcrflag =''C'' THEN Tramt ELSE 0 END AS Dr, CASE WHEN Drcrflag =''D'' THEN Tramt ELSE 0 END AS Cr
from Trans_Transnarr
WHERE (Trtype=''CPM'' or Trtype=''CRT'') and Loccode=''' + @Loccode + ''' and
Refdt>=Convert(Datetime,''' + @SDt + ''' ,103) and Refdt<=Convert(Datetime,''' + @EDt + ''',103)) TT
Group By Glcode,Trtype) X'

Set @MainSQry='Select Glcode,Trtype,Refdt,Refno,Narration,Dr,Cr Into #Gltmp from ('

Declare @Fullqry NVarchar(4000)

If @OP='WOB'
Begin
Set @TrnQry = @OpBalQry+@TrnQry
End

Set @FullQry = @MainSQry+@TrnQry

Exec sp_executesql @FullQry

Select Glcode,Trtype,Refdt,Refno,Narration,Ltrim(Rtrim(Str(Dr,14,2))) as Dr,Ltrim(Rtrim(Str(Cr,14,2))) as Cr from #Gltmp SQL2XML
Where Dr+Cr<>0 Order By Glcode,Trtype,Refdt,Refno for XML AUTO

Drop Table #Gltmp
GO

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 01:57:35

IF EXISTS (Select 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='#Gltmp')
Begin
Drop Table #Gltmp
End

You don't need that. The # TABLE won't be in scope.

Why are you using dynamic SQL here? (There's a lot of code, its a bit hard to see what its doing).

If you are going to use sp_ExecuteSQL it would be better to use a parameterized query, rather than just a straight SQL string (so that the more frequently used query patterns get reused from cache)

Kristen

nirene
Yak Posting Veteran

98 Posts

Posted - 2007-09-28 : 23:40:21

Hello Kristen,
Sorry for the delayed reply.Yes #tables will not be in scope,I had already removed those lines from my SP.

My SP looks a bit complicated cos that is how my client's format look like.It is a General Ledger format for a Financial Accoutning system of our client.I'm using sp_ExecuteSQL cos if the user opts for with opening balance the opening balance string will be added to TRNQRY string if no it will not be added.

Please advice me of how to go about in extracting the data faster.This SP is executed thru a web application.

Nirene

Kristen
Test

22859 Posts

Posted - 2007-09-29 : 04:42:43

You don;t need dynamic SQL, which makes this whole query much harder to read. Just add a condition to the UNION for the scenario where the user wants the opening balance query, or pull the main data into a temporary table, and then conditionally insert the opening balance query into the temporary table, and then retrieve from that.

If you do that your WHERE clause will change from

WHERE
(A.Glcode=B.Glcode and B.Cocode='SomeString' and A.Refdt>=Convert(Datetime,'SomeDate',103)
and A.Refdt<=Convert(Datetime,'SomeDate' ,103)
and A.Loccode='SomeString') and (B.Subgroupcode='BANK' or B.Subgroupcode='CASH')

which won't reuse query plan caching effectively, to this:

WHERE
(A.Glcode=B.Glcode and B.Cocode= @Cocode +and A.Refdt>=Convert(Datetime, @SDt, 103)
and A.Refdt<=Convert(Datetime, @EDt, 103)
and A.Loccode= @Loccode) and (B.Subgroupcode='BANK' or B.Subgroupcode='CASH')

which is much more likely to reuse a cached query plan.

But IMO its such a mess it really needs completely rewriting.

For example, the use of SELECTs FROM a Sub-SELECT makes it confusing, and I don't think they are needed.

Things like this
Convert(Datetime,''' + @SDt + ''',103)
are repeated within the code, better to pre-process @SDt into a DATETIME variable first. Better still have the application pass a datetime variable in the first place.

And do you really need UNION instead of UNION ALL? which would be much more efficient.

And you would be better off using JOINS rather than Table lists and conditions in the WHERE clause.

And using functions on columns adversely effects performance, so this:
(A.Slcode IS NULL OR Len(Rtrim(Ltrim(A.Slcode)))=0)
ought to be sorted out properly. Put some validation in place so that data cannot get into the database in this adhoc fashion. You appear to be allowing Null, Empty and Variable lengths strings of spaces; better to disallow all that lot and only allow NULL or a real, non-blank, value.

If you genuinely need Blank and NULL then at least get rid of superfluous trailing spaces unless they are critical to the data.

Anyway, you don't need half of those functions. LTRIM or RTRIM on a bunch of spaces is going to do the job, you don't need both.

Anyway, you don't need either of those if A.Slcode is VARCHAR (rather than CHAR) datatype

(A.Slcode IS NULL OR A.Slcode ='')

will do.

And what sort of checking of the query plan has been performed? Any tests with different indexes, and so on?

SELECT A.Glcode,'''' as Trtype, Convert(Varchar,A.Refdt,103)

You should do the presentation of the data in at the front end / application, not in SQL Server. And you haven't specified the size for VARCHAR so you are running with a default setting, which may catch you out in the future.

(G.Subgroupcode<>''BANK'' or G.Subgroupcode<>''CASH''))

may be more efficient as G.Subgroupcode NOT IN ('BANK', 'CASH') and would benefit from including in a JOIN clause (described above), in particular if the index on Glmast can "cover" that column.

"Please advice me of how to go about in extracting the data faster. This SP is executed through a web application."

How much data is being returned? Because if its hundreds of rows its going to be slow in a web application, regardless of how quickly SQL Server can retrieve the data.

Check how long it takes to just run the SQL, and compare that with the rendering time on a browser.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources