Viewing 15 posts - 37,696 through 37,710 (of 49,552 total)
Mel Harbour (7/13/2009)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 13, 2009 at 9:45 am
That looks a little better already.
Try adding an index on the temp table. Put this on before you insert the data into the temp table.
CREATE CLUSTERED INDEX idx_TopScores_RowNumberUserID ON...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 13, 2009 at 9:35 am
Case is only used within a query. You'll have to use IF statements, like what Terrance has shown you.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 13, 2009 at 9:19 am
As a first try at indexes, and without seeing any of the other queries against this table..
Add an index:
PointsCategory, UserID INCLUDE Date, PointsScored
I know there's a similar existing index, but...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 13, 2009 at 9:13 am
Mel Harbour (7/13/2009)
It would be relatively straightforward to split it into running two completely separate SPs for the two cases.
As a first step, before getting into indexing, I would suggest...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 13, 2009 at 9:09 am
Ouch. 100000 rows in a table variable, a clustered index scan on the users table and a seek that's returning most of the table on the points... Not fun.
Dunno if...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 13, 2009 at 8:58 am
Dave Ballantyne (7/13/2009)
You can lead a horse to water but you cant force him to drinkhttp://www.sqlservercentral.com/Forums/Topic751918-8-2.aspx?Update=1
On second thoughts, I think I'm leaving that thread alone...
tatcalltype ='null'
Why.....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 13, 2009 at 8:49 am
Dave Ballantyne (7/13/2009)
Obviously 99% of the users have no updates throughout the...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 13, 2009 at 8:48 am
Hi Mel
Could you please post index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
😉
What's the reasoning behind this?
WHERE PointsCategory = ISNULL(@PointsCategory, PointsCategory)
That kind of construct tends to play havok with indexing....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 13, 2009 at 8:40 am
andresysbl (7/13/2009)
mmmm
exec sp_attach_single_file_db 'MYDATABASENAME',
'PATH and NAME DATABASE FILE'
Example:
exec sp_attach_single_file_db 'test',
'c:\test.mdf'
Deprecated in SQL 2005, will be removed in a future version. Rather use CREATE DATABASE ... FOR ATTACH or CREATE DATABASE...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 13, 2009 at 7:43 am
Can you post index definitions please? From the plan there looks like an obvious index but without seeing the existing indexes....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 13, 2009 at 7:37 am
river (7/13/2009)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 13, 2009 at 7:05 am
ps (7/13/2009)
GilaMonster (7/13/2009)
mysticslayer (7/12/2009)
The issue here is that I get an parameter filled with quotes.Not quite sure what you mean here. Could you give more detail?
Perhaps he's getting data...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 13, 2009 at 4:12 am
mysticslayer (7/12/2009)
The issue here is that I get an parameter filled with quotes.
Not quite sure what you mean here. Could you give more detail?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 13, 2009 at 3:58 am
Try making the parameter a varbinary instead of a varchar.
ALTER PROCEDURE [dbo].[sp_example]
(
...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 13, 2009 at 3:55 am
Viewing 15 posts - 37,696 through 37,710 (of 49,552 total)