28,280 reputation
771121
bio website
location United Kingdom
age
visits member for 3 years
seen 6 hours ago

Email: stackoverflowmartinsmith@gmail.com


Oct
22
comment Invalid object when selecting from temporary table
If you are going to give it a unique name when creating it how will you reference it? More dynamic SQL?
Oct
22
comment Invalid object when selecting from temporary table
"Why is this happening?" because local temporary tables are dropped automatically when they go out of scope. Is there an implicit second part to this question "What can I do about it?" if so please show more code or give us some context as to why you are creating it dynamically and how you are using it afterwards.
Oct
20
comment Stored procedure takes 18 seconds on first load, 0 seconds on subsequent loads, waiting several minutes makes it slow again
Well if you aren't seeing recompiles presumably these are fresh compiles then and for some reason (perhaps memory pressure) the plans aren't being preserved in the cache.
Oct
20
comment Stored procedure takes 18 seconds on first load, 0 seconds on subsequent loads, waiting several minutes makes it slow again
@nicholmikey though I see you mention some data wizard profiler in the question. Why are you using that? Are you on Express edition? If so please check the value of auto_close. It defaults to on for Express. Closing the DB will lose all the plans for it.
Oct
20
comment Stored procedure takes 18 seconds on first load, 0 seconds on subsequent loads, waiting several minutes makes it slow again
If the reason for recompile is a "correctness" based one rather than an optimality based one there's no way of preventing the recompiles. You can trace them to see the reason.
Oct
20
comment Stored procedure takes 18 seconds on first load, 0 seconds on subsequent loads, waiting several minutes makes it slow again
Possibly one of the many tables you reference encounters frequent schema changes, invalidating the plan?
Oct
20
comment Stored procedure takes 18 seconds on first load, 0 seconds on subsequent loads, waiting several minutes makes it slow again
@nicholmikey that references a lot of tables. What is the compile time?
Oct
20
comment Stored procedure takes 18 seconds on first load, 0 seconds on subsequent loads, waiting several minutes makes it slow again
Well the OP says that if they wait 3 minutes performance goes back to poor. Hence 180 secs.
Oct
20
comment Stored procedure takes 18 seconds on first load, 0 seconds on subsequent loads, waiting several minutes makes it slow again
A PLE of 180 seconds would be painfully low though. Hence wondering about auto close.
Oct
20
comment How can I use expressions as arguments to EXEC?
Though in the first case you can do RAISERROR('String %s',1,0,@variable);
Oct
20
comment Stored procedure takes 18 seconds on first load, 0 seconds on subsequent loads, waiting several minutes makes it slow again
Is the database set to auto_close? What is the output from set statistics io on;set statistics time on for slow and fast runs?
Oct
20
comment How can I use expressions as arguments to EXEC?
Relevant connect item connect.microsoft.com/SQLServer/feedback/details/352110/…
Oct
20
comment Why does a UNIQUE constraint allow only one NULL?
@GreenstoneWalker - It doesn't take the "safe" route. It assumes that the missing value won't conflict. CREATE TABLE #T(A INT NULL UNIQUE);INSERT INTO #T VALUES (1),(NULL);UPDATE #T SET A = 1 WHERE A IS NULL; will raise an error. According to your theory of the design motivations it ought to have prevented the insertion of NULL in the first case - because the incomplete knowledge means there is no guarantee that the value is different.
Oct
20
comment how to order a field based on two parts of the row value
What DBMS. Why isn't this two separate columns?
Oct
20
comment Why does a UNIQUE constraint allow only one NULL?
Allowing a single null doesn't achieve this goal either. Because the missing value might turn out to be the same as the value in one of the other rows.
Oct
19
revised Strange query results for DELETE with ROW_NUMBER
added 343 characters in body
Oct
19
comment Strange query results for DELETE with ROW_NUMBER
There's nothing unsafe or un set based about deleting with a window function. Often it is the most efficient way. The semantics of the delete operation are fine here. The only issue is the unexpected re-application of the row numbering when computing the output result.
Oct
19
comment Strange query results for DELETE with ROW_NUMBER
But you might as well suggest keeping the original code and removing the output clause as a "workaround" then. The only issue that needs working around is the unexpected result of that.
Oct
19
awarded  Enlightened
Oct
19
awarded  Nice Answer