Stored Procedure Optmimization Help needed

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

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Procedure Optmimization Help needed

Author

Topic

am_srinivas
Starting Member

1 Post

Posted - 2007-09-20 : 01:28:16

Hi,

I have a query which i am copying it down. Can anybody tell me if there is any scope of optimization?

"SELECT
Pt1.ProjectID,
CAST(E AS varchar) AS Eligible,
CAST(EA AS varchar) AS EligibleAlt,
CAST(NC AS varchar) AS NotCalled,
CAST(NCA AS varchar) AS NotCalledAlt,
Calling,
CAST(C AS varchar) As Called,
CAST(CA AS varchar) AS CalledAlt,
CAST(CBT AS varchar) As CBToday,
CAST(CBTA AS varchar) AS CBTodayAlt,
CAST(CBB AS varchar) as CBBeyond,
CAST(CBBA AS varchar) AS CBBeyondAlt,
LastImport
FROM (SELECT ProjectID,LastImport FROM Project WITH (NOLOCK) WHERE ProjectID IN(Select value from listtotable(@pProjectID,','))) Pt1
LEFT JOIN
(SELECT ProjectID,
COUNT(CASE WHEN CallFlag=1 AND Status<>1 AND CBDateTime<CONVERT(datetime,@pDateTime,101) AND
AgentID=0 THEN 1 ELSE Null END) AS NC,
COUNT(CASE WHEN CallFlag=0 THEN 1 ELSE Null END) AS C,
COUNT(CASE WHEN CallFlag=1 AND CBDateTime>CONVERT(datetime,@pDateTime,101) AND
CBDateTime<=CONVERT(datetime,@pDayEndTime,101) THEN 1 ELSE Null END) AS CBT,
COUNT(CASE WHEN CallFlag=1 AND Status<>1 AND CBDateTime<CONVERT(datetime,@pDateTime,101) AND
AgentID=0 AND TimeZone IN (Select value from listtotable(@pTimeZomes,',')) THEN 1 ELSE Null END) AS E,
COUNT(CASE WHEN Status=1 AND SystemID=@tintSystemID THEN 1 ELSE Null END) AS Calling,
COUNT(CASE WHEN CallFlag=1 AND CBDateTime>CONVERT(datetime,@pDayEndTime,101) THEN 1 ELSE Null END) AS CBB
FROM Dial WITH (NOLOCK) GROUP BY ProjectID) Pt2
ON (Pt1.ProjectID=Pt2.ProjectID)
LEFT JOIN
(SELECT ProjectID,
COUNT(CASE WHEN D.DialID=DA.DialID AND DA.CallFlag=1 AND D.Status<>1 AND D.AgentID = 0 AND
DA.CBDateTime<CONVERT(datetime,@pDateTime,101) AND DA.TimeZone IN (-1) THEN 1 ELSE Null END) AS EA,
COUNT(CASE WHEN D.DialID=DA.DialID AND DA.CallFlag=1 AND D.Status<>1 AND D.AgentID = 0 AND
DA.CBDateTime<CONVERT(datetime,@pDateTime,101) THEN 1 ELSE Null END) AS NCA,
COUNT(CASE WHEN D.DialID=DA.DialID AND (DA.CallFlag=0 OR D.AgentID<>0) THEN 1 ELSE Null END) AS CA,
COUNT(CASE WHEN D.DialID=DA.DialID AND DA.CallFlag=1 AND DA.CBDateTime>CONVERT(datetime,@pDateTime,101)
AND DA.CBDateTime<=CONVERT(datetime,@pDayEndTime,101) THEN 1 ELSE Null END) AS CBTA,
COUNT(CASE WHEN D.DialID=DA.DialID AND DA.CallFlag=1 AND DA.CBDateTime>CONVERT(datetime,@pDayEndTime,101)
THEN 1 ELSE Null END) AS CBBA
FROM Dial D WITH (NOLOCK) LEFT JOIN DialAlt DA WITH (NOLOCK) ON (D.DialID=DA.DialID) GROUP BY ProjectID)
Pt3 ON (Pt1.ProjectID=Pt3.ProjectID)"

When i am trying to convert the above query to strored procedure i am getting the following error:
"Msg 130, Level 15, State 1, Procedure GetDialRecords1, Line 30
Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

Please help me in either solving this error or if there is any alternative for this query. The dial table has more than 300,000 records. So when i am trying to fire this query from frontend VB application it taking lot of time and as time elapses this query creates lot of load. I saw that in the profiler.

Maruti Srinivas

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-09-20 : 02:22:49

Hi,

SubQuery is not allowed in aggregates, u need to remove
AND TimeZone IN (Select value from listtotable(@pTimeZomes,','))

and use left join instead of it

FROM Dial WITH (NOLOCK) D
LEFT JOIN (Select value from listtotable(@pTimeZomes,',')) T ON T.Value = D.TimeZone
GROUP BY D.ProjectID

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-20 : 04:25:56

Also, if you dont specify length for varchar, by default 30 would be taken

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-20 : 04:52:32

[code]FROM Project AS pt1 WITH (NOLOCK)
INNER JOIN ListToTable(@pProjectID, ',') AS q ON q.Value = pt1.ProjectID[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-20 : 04:52:51

And why are you converting COUNTS (numeric) to VARCHAR at all?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-20 : 05:06:39

[code]SELECT @pDateTime = DATEADD(DAY, DATEDIFF(DAY, 0, @pDateTime), 0),
@pDayEndTime = DATEADD(DAY, DATEDIFF(DAY, 0, @pDayEndTime), 0)

SELECT pt1.ProjectID,
pt2.Eligible,
pt3.EligibleAlt,
pt2.NotCalled,
pt3.NotCalledAlt,
pt2.Calling,
pt2.Called,
pt3.CalledAlt,
pt2.CBToday,
pt3.CBTodayAlt,
pt2.CBBeyond,
pt3.CBBeyondAlt,
pt1.LastImport
FROM Project AS pt1 WITH (NOLOCK)
INNER JOIN ListToTable(@pProjectID, ',') AS q ON q.Value = pt1.ProjectID
LEFT JOIN (
SELECT d.ProjectID,
SUM(CASE WHEN d.CallFlag = 1 AND d.Status <> 1 AND d.CBDateTime < @pDateTime AND d.AgentID = 0 THEN 1 ELSE 0 END) AS NotCalled,
SUM(CASE WHEN d.CallFlag = 0 THEN 1 ELSE 0 END) AS Called,
SUM(CASE WHEN d.CallFlag = 1 AND d.CBDateTime >= DATEADD(DAY, 1, @pDateTime) AND d.CBDateTime < DATEADD(DAY, 1, @pDayEndTime) THEN 1 ELSE 0 END) AS CBToday,
SUM(CASE WHEN d.CallFlag = 1 AND d.Status <> 1 AND d.CBDateTime < @pDateTime AND d.AgentID = 0 AND q.Value IS NOT NULL THEN 1 ELSE 0 END) AS Eligible,
SUM(CASE WHEN d.Status = 1 AND d.SystemID = @tintSystemID THEN 1 ELSE 0 END) AS Calling,
SUM(CASE WHEN d.CallFlag = 1 AND d.CBDateTime >= DATEADD(DAY, 1, @pDayEndTime) THEN 1 ELSE 0 END) AS CBBeyond
FROM Dial AS d WITH (NOLOCK)
LEFT JOIN ListToTable(@pTimeZomes, ',') AS q ON q.Value = d.TimeZone
GROUP BY d.ProjectID
) AS pt2 ON pt2.ProjectID = pt1.ProjectID
LEFT JOIN (
SELECT ProjectID,
SUM(CASE WHEN da.CallFlag = 1 AND d.Status <> 1 AND d.AgentID = 0 AND da.CBDateTime < @pDateTime AND da.TimeZone = -1 THEN 1 ELSE 0 END) AS EligibleAlt,
SUM(CASE WHEN da.CallFlag = 1 AND d.Status <> 1 AND d.AgentID = 0 AND da.CBDateTime < @pDateTime THEN 1 ELSE 0 END) AS NotCalledAlt,
SUM(CASE WHEN (da.CallFlag = 0 OR d.AgentID <> 0) THEN 1 ELSE 0 END) AS CalledAlt,
SUM(CASE WHEN da.CallFlag = 1 AND da.CBDateTime >= DATEADD(DAY, 1, @pDateTime) AND da.CBDateTime < DATEADD(DAY, 1, @pDayEndTime) THEN 1 ELSE 0 END) AS CBTodayAlt,
SUM(CASE WHEN da.CallFlag = 1 AND da.CBDateTime >= DATEADD(DAY, 1, @pDayEndTime) THEN 1 ELSE 0 END) AS CBBeyondAlt
FROM Dial AS D WITH (NOLOCK)
LEFT JOIN DialAlt AS da WITH (NOLOCK) ON da.DialID = d.DialID
GROUP BY ProjectID
) AS pt3 ON pt3.ProjectID = pt1.ProjectID[/code]

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-20 : 19:58:22

"Can anybody tell me if there is any scope of optimization?"

Make sure that

listtotable(@pProjectID,',')

is using an efficient method.

See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Best%20split%20functions

Kristen

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-09-21 : 23:35:37

quote:

Originally posted by Peso

SELECT	@pDateTime = DATEADD(DAY, DATEDIFF(DAY, 0, @pDateTime), 0),
	@pDayEndTime = DATEADD(DAY, DATEDIFF(DAY, 0, @pDayEndTime), 0)

SELECT		pt1.ProjectID,
		pt2.Eligible,
		pt3.EligibleAlt,
		pt2.NotCalled,
		pt3.NotCalledAlt,
		pt2.Calling,
		pt2.Called,
		pt3.CalledAlt,
		pt2.CBToday,
		pt3.CBTodayAlt,
		pt2.CBBeyond,
		pt3.CBBeyondAlt,
		pt1.LastImport
FROM		Project AS pt1 WITH (NOLOCK)
INNER JOIN	ListToTable(@pProjectID, ',') AS q ON q.Value = pt1.ProjectID
LEFT JOIN	(
			SELECT		d.ProjectID,
					SUM(CASE WHEN d.CallFlag = 1 AND d.Status <> 1 AND d.CBDateTime < @pDateTime AND d.AgentID = 0 THEN 1 ELSE 0 END) AS NotCalled,
					SUM(CASE WHEN d.CallFlag = 0 THEN 1 ELSE 0 END) AS Called,
					SUM(CASE WHEN d.CallFlag = 1 AND d.CBDateTime >= DATEADD(DAY, 1, @pDateTime) AND d.CBDateTime < DATEADD(DAY, 1, @pDayEndTime) THEN 1 ELSE 0 END) AS CBToday,
					SUM(CASE WHEN d.CallFlag = 1 AND d.Status <> 1 AND d.CBDateTime < @pDateTime AND d.AgentID = 0 AND q.Value IS NOT NULL THEN 1 ELSE 0 END) AS Eligible,
					SUM(CASE WHEN d.Status = 1 AND d.SystemID = @tintSystemID THEN 1 ELSE 0 END) AS Calling,
					SUM(CASE WHEN d.CallFlag = 1 AND d.CBDateTime >= DATEADD(DAY, 1, @pDayEndTime) THEN 1 ELSE 0 END) AS CBBeyond
			FROM		Dial AS d WITH (NOLOCK)
			LEFT JOIN	ListToTable(@pTimeZomes, ',') AS q ON q.Value = d.TimeZone
			GROUP BY	d.ProjectID
		) AS pt2 ON pt2.ProjectID = pt1.ProjectID
LEFT JOIN	(
			SELECT		ProjectID,
					SUM(CASE WHEN da.CallFlag = 1 AND d.Status <> 1 AND d.AgentID = 0 AND da.CBDateTime < @pDateTime AND da.TimeZone = -1 THEN 1 ELSE 0 END) AS EligibleAlt,
					SUM(CASE WHEN da.CallFlag = 1 AND d.Status <> 1 AND d.AgentID = 0 AND da.CBDateTime < @pDateTime THEN 1 ELSE 0 END) AS NotCalledAlt,
					SUM(CASE WHEN (da.CallFlag = 0 OR d.AgentID <> 0) THEN 1 ELSE 0 END) AS CalledAlt,
					SUM(CASE WHEN da.CallFlag = 1 AND da.CBDateTime >= DATEADD(DAY, 1, @pDateTime) AND da.CBDateTime < DATEADD(DAY, 1, @pDayEndTime) THEN 1 ELSE 0 END) AS CBTodayAlt,
					SUM(CASE WHEN da.CallFlag = 1 AND da.CBDateTime >= DATEADD(DAY, 1, @pDayEndTime) THEN 1 ELSE 0 END) AS CBBeyondAlt
			FROM		Dial AS D WITH (NOLOCK)
			LEFT JOIN	DialAlt AS da WITH (NOLOCK) ON da.DialID = d.DialID
			GROUP BY	ProjectID
		) AS pt3 ON pt3.ProjectID = pt1.ProjectID

E 12°55'05.25"
N 56°04'39.16"

Ah... finally... readable, formatted code

--Jeff Moden

Kristen
Test

22859 Posts

Posted - 2007-09-22 : 02:27:17

That's PPP mate ... Peso Pretty Print

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-09-22 : 10:58:09

Didn't know what you guys called it, but I'm well familiar with Peter's fine formating technique's.

--Jeff Moden

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-09-24 : 17:37:14

Just to add a comment about Peso's query and the use of table valued functions.

You may find that loading a temp table or table variable with the results of a table values function (in this case ListToTable) and then performing the join will make the query more effecient.

Kristen
Test

22859 Posts

Posted - 2007-09-25 : 02:24:17

You got a link to any comparisons?

My gut feeling is that JOINing to a table-value-function shouldn;t be any different to JOINing to a TEMP table ...

... but if I'm wrong we'll have a shed-load of code to change

Kristen

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-09-25 : 11:54:37

Just real world experience. I'll see if I can generate some numbers and/or a sample for you.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-09-25 : 13:12:18

If you are joing to small tables then there may be little advantage to putting the table-valued function data in a temp table. I looked at some code that uses a split UDF and a few are hardly affected by the change, but all were impoved by joining to a temp table. Here is a bit of data for comparison, sorry I don't have sample data for this sort of test as a small sample set doesn't show anything.

Here are the IO Statistics for the same query one using a table variable populated with the results from a table-valued UDF and the other with a join directly to the UDF. For this query the UDF returns 3 rows (pretty minimal).

-- Join to UDF
Table '#3689EB4E'. Scan count 8, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dim1'. Scan count 0, logical reads 342, physical reads 9, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dim2'. Scan count 0, logical reads 114, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dim3'. Scan count 0, logical reads 114, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Fact1'. Scan count 5, logical reads 7057, physical reads 0, read-ahead reads 3211, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dim4'. Scan count 0, logical reads 114, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table1'. Scan count 23, logical reads 131, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Fact2'. Scan count 5, logical reads 18343, physical reads 0, read-ahead reads 14008, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Fact3'. Scan count 5, logical reads 23094, physical reads 0, read-ahead reads 18562, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Fact4'. Scan count 5, logical reads 33379, physical reads 0, read-ahead reads 29172, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Fact5'. Scan count 5, logical reads 40937, physical reads 0, read-ahead reads 35943, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Fact6'. Scan count 5, logical reads 51490, physical reads 0, read-ahead reads 46551, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Fact7'. Scan count 5, logical reads 63615, physical reads 0, read-ahead reads 58004, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Fact8'. Scan count 5, logical reads 30373, physical reads 0, read-ahead reads 30373, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

-- Join the table variable.
Table '#321A3BEC'. Scan count 8, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dim1'. Scan count 0, logical reads 342, physical reads 9, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dim2'. Scan count 0, logical reads 114, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dim3'. Scan count 0, logical reads 114, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Fact1'. Scan count 5, logical reads 7057, physical reads 0, read-ahead reads 7064, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dim4'. Scan count 0, logical reads 114, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table1'. Scan count 23, logical reads 129, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Fact2'. Scan count 5, logical reads 18343, physical reads 0, read-ahead reads 18350, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Fact3'. Scan count 5, logical reads 23094, physical reads 0, read-ahead reads 23106, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Fact4'. Scan count 5, logical reads 33379, physical reads 0, read-ahead reads 33386, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Fact5'. Scan count 5, logical reads 40937, physical reads 0, read-ahead reads 40945, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Fact6'. Scan count 5, logical reads 51490, physical reads 0, read-ahead reads 51497, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Fact7'. Scan count 5, logical reads 63615, physical reads 0, read-ahead reads 63629, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Fact8'. Scan count 3, logical reads 18, physical reads 13, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

-- extra overhead of loading the table variable
Table '#321A3BEC'. Scan count 0, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#3402845E'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Also, looking at the query plan the sub-tree costs are slightly different: 221.246 compared to 196.846 for the table variable.

This may be bad example because the indexing for this query is not there. But, I've seen it make some pretty noticable differences, but that is probably when you are splitting on more values. So, as with most things, I'd try both ways and see if it makes an improvement in your environment for your data. If you want to see any other metrics just let me know.

-Ryan

EDIT: I forgot to mention this is on 2005 64 bit.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-09-25 : 14:35:14

for the OP, who seems to have disappeared:

are you aware that the NOLOCK hint means you are doing dirty reads? Where you getting deadlocks or something before you added the hint?

elsasoft.org

Kristen
Test

22859 Posts

Posted - 2007-09-26 : 05:41:44

@Lamprey

"some code that uses a split UDF"

We discovered, recently, that our split function had DISASTROUS performance

when used with long delimited lists. Just in case you a re in the same boat you might want to review the tail end of this thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Best%20split%20functions

Your real world experience tells me I should go check some of our big-table stuff that involves UDFs.

Just to double check I'm on the right page here!

You are talking about replacing


SELECT *
FROM dbo.MyUDF(@Param1, @Param2) AS U
     JOIN dbo.MyTable AS T
         ON T.SomeID = U.SomeID

with


DELCARE @Temp TABLE
(
    SomeID int NOT NULL,
    OtherCol int NULL,
    PRIMARY KEY
    (
        SomeID
    )
)
INSERT INTO @Temp
SELECT *
FROM
dbo.MyUDF(@Param1, @Param2)

SELECT *
FROM @Temp AS U
     JOIN dbo.MyTable AS T
         ON T.SomeID = U.SomeID

have I got that right?

Kristen

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-09-26 : 12:52:50

Yes, that is correct! :)

One thing to consider is how the table the UDF returns is constructed. Now, I have not spent any time investigating this, but I suspect that if you cluster on the Value part of the split, then that might gain you some performance back. Meaning, if the returning table has two columns ID and Val then clustering on Val (and even removing the ID) might enable SQL to take advantage of the index. But, it seems that a lot of people like to keep the UDF more generic and in that case I think it is more preferment to load the retuning table into a table variable.

EDIT: I did a quick test by placing a PRIMARY CLUSTERED KEY on the return table and it did not make any difference.
I'll see if I can spare a couple of minutes and work up a new UDF into the query that I ran for the metrics above and see if there is any performance difference.

Kristen
Test

22859 Posts

Posted - 2007-09-26 : 15:43:23

"Meaning, if the returning table has two columns ID and Val then clustering on Val (and even removing the ID) might enable SQL to take advantage of the index"

Interesting. We ALWAYS, and I mean ALWAYS put an index on all our temporary tables.

but we haven;t got on on the RETURN TABLE from our Split function and YES we will always JOIN on the Value and YES we DO have an ItemNo column ... definitely food for though, thanks.

Kristen

johnsql
Posting Yak Master

161 Posts

Posted - 2007-09-26 : 16:34:37

quote:
Originally posted by Peso

FROM Project AS pt1 WITH (NOLOCK)
INNER JOIN ListToTable(@pProjectID, ',') AS q ON q.Value = pt1.ProjectID

E 12°55'05.25"
N 56°04'39.16"

johnsql
Posting Yak Master

161 Posts

Posted - 2007-09-26 : 16:36:30

quote:
Originally posted by Peso

FROM Project AS pt1 WITH (NOLOCK)
INNER JOIN ListToTable(@pProjectID, ',') AS q ON q.Value = pt1.ProjectID

E 12°55'05.25"
N 56°04'39.16"

I have a short question over here. What is the purpose of using "WITH (NOLOCK)" inside a select statement? Performance?
Thanks for your explanation.
johnsql

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-09-26 : 19:35:36

More or less, yes... it means that it will do dirty reads on possibly uncommitted data instead of waiting.

--Jeff Moden

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources