Large query with logic and filtering: SP, UDF, or View?

Question

Large query with logic and filtering: SP, UDF, or View?

Viewing 8 posts - 16 through 22 (of 22 total)

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

Steve Fontaine SSC-Addicted Points: 415 More actions · Answer 1

Well...sorry, what I was saying was I was only able to copy what I showed you. The output text shows up as a set of records and I had to copy the records' text, and it only gave me up to where I added the ellipsis. If there is a way to get the whole thing, I'm listening.

In tblOriginal, indexes are FileID (PK, clustered) and Filename (non-clustered).

In tblCorrections, index is just FileID (PK, clustered).

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Use results in text,

also go in tools/options/results/max number of characters per column = 2000 (instead of 255 default).

Steve Fontaine SSC-Addicted Points: 415 More actions · Answer 3

StmtText

---------------------------------------------------------------------------------------

SELECT * FROM vwBigTable where starttime > '4/4/05' AND stoptime < '7/6/05'

(1 row(s) affected)

StmtText

------------------------

|--Compute Scalar(DEFINE: ([Expr1004]=If ([tblCorrections].[FileID]=NULL) then [tblOriginal].[Field1] else [tblCorrections].[Field1], [Expr1005]=If ([tblCorrections].[FileID]=NULL) then [tblOriginal].[StartTime] else [tblCorrections].[StartTime], [Expr1006]=If ([tblCorrections].[FileID]=NULL) then [tblOriginal].[StopTime] else [tblCorrections].[StopTime], [Expr1007]=If ([tblCorrections].[FileID]=NULL) then [tblOriginal].[Field2] else [tblCorrections].[Field2], [Expr1008]=If ([tblCorrections].[FileID]=NULL) then [tblOriginal].[Field3] else [tblCorrections].[Field3], [Expr1009]=If ([tblCorrections].[FileID]=NULL) then [tblOriginal].[Field4] else [tblCorrections].[Field4], [Expr1010]=If ([tblCorrections].[FileID]=NULL) then [tblOriginal].[Field4] else [tblCorrections].[Field4], [Expr1011]=If ([tblCorrections].[FileID]=NULL) then [tblOriginal].[Field5] else [tblCorrections].[Field5], [Expr1012]=If ([tblCorrections].[FileID]=NULL) then [tblOriginal].[Field6] else [tblCorrections].[Field6]))

|--Filter(WHERE: (If ([tblCorrections].[FileID]=NULL) then [tblOriginal].[StartTime] else [tblCorrections].[StartTime]>Convert([@1]) AND If ([tblCorrections].[FileID]=NULL) then [tblOriginal].[StopTime] else [tblCorrections].[StopTime]<Convert([@2])))

|--Merge Join(Right Outer Join, MERGE: ([tblCorrections].[FileID])=([tblOriginal].[FileID]), RESIDUAL: ([tblOriginal].[FileID]=[tblCorrections].[FileID]))

|--Clustered Index Scan(OBJECT: ([MyDatabase].[dbo].[tblCorrections].[PK_tblCorrections]), ORDERED FORWARD)

|--Clustered Index Scan(OBJECT: ([MyDatabase].[dbo].[tblOriginal].[PK_tblOriginal]), ORDERED FORWARD)

(5 row(s) affected)