June 22, 2015 at 5:25 am
Hi was hoping some one could shed light on this.
I have a table that receives data, this i called my transactions table.
The table column config is :-
transactionID(PK, int, not null)
siteID(PK, int, not null)
trans_time(datetime, not null)
site_time(datetime, not null)
gamename(varchar(6), not null)
gamename(varchar(40), not null)
sitenamet(varchar(40), not null)
I then have a ASP.net page that uses two datetime pickers on it, and call a stored proc to search and return data between the two dates selected by the datetime pickers.
The stored proc is :-
CREATE PROCEDURE [dbo].[spReport_GetTransData]
-- Add the parameters for the stored procedure here
@EndDate datetime,
@StartDate datetime,
AS
BEGIN
SET NOCOUNT ON
SELECTtransactions.areacode,
sites.sitecode,
transactions.sitename,
transactions.gamename,
CONVERT(varchar(12), transactions.trans_time, 103),
longterm.meter1,
longterm.meter2,
longterm.meter3,
longterm.meter4,
longterm.meter5,
accounts.cashin,
accounts.totalwon,
accounts.ticketout,
accounts.vtp,
dongle.[current]
FROMtransactions INNER JOIN
sites ON transactions.siteID = sites.siteID INNER JOIN
dongle ON transactions.transactionID = dongle.transactionID INNER JOIN
longterm ON transactions.transactionID = longterm.transactionID INNER JOIN
accounts ON transactions.transactionID = accounts.transactionID
WHERE (transactions.trans_time BETWEEN @StartDate AND @EndDate)
The issue i have is it can take upto 3-4mins to return the query with the data. The transactions table only currently has 140000 records in and only about 800 new transactions a month.
Any help on speeding this up would be greatly appreciated 🙂
June 22, 2015 at 5:26 am
Index definitions and execution plan (saved as a .sqlplan file) please.
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
June 22, 2015 at 8:52 am
GilaMonster (6/22/2015)
Index definitions and execution plan (saved as a .sqlplan file) please.
I'm not ofay on how to do this, i'm not a SQL developer i come from an embedded software world. The Database is running on SQL2003 Express.
and thus alot of it was manually created some years ago.
June 22, 2015 at 8:55 am
Err, there's no such version as SQL 2003.
You mean SQL 2005?
See http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ for saving execution plans.
For the index definitions, you can see them in Management Studio, should be same place you get the table definition from.
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
June 22, 2015 at 9:27 am
GilaMonster (6/22/2015)
Err, there's no such version as SQL 2003.You mean SQL 2005?
See http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ for saving execution plans.
For the index definitions, you can see them in Management Studio, should be same place you get the table definition from.
sorry men't 2005. I'll have alook following your link 🙂
June 23, 2015 at 3:26 am
Attached is the sqlplan, not sure how to get the other info you wanted.
June 23, 2015 at 3:41 am
I need to know what indexes exist on the tables involved in this query.
Open up Management Studio, navigate in Object Explorer to the tables and script out the 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
June 23, 2015 at 4:06 am
GilaMonster (6/23/2015)
I need to know what indexes exist on the tables involved in this query.Open up Management Studio, navigate in Object Explorer to the tables and script out the indexes.
coming from a non SQL developer background i dont know how to do this ??
June 23, 2015 at 4:12 am
Management Studio is what you must have used to get the execution plan. There's a window called Object Explorer (open by default). It's a tree-structure of your database.
Navigate the tree structure to the tables used in this query. Expand out the table. Expand out Indexes. Right click each index and chose script -> Create -> To clipboard. then paste into notepad or similar. Repeat for each index on each of the tables used in this query.
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
June 23, 2015 at 7:01 am
right got it, its attached, thanks for the help 🙂
June 23, 2015 at 7:27 am
Cool, thank you.
Ok, definitely index-related. You just don't have the indexes needed to support this query. With so few indexes, I'm not too concerned about adding more.
Add these indexes and your query's performance should improve significantly.
CREATE NONCLUSTERED INDEX idx_Transactions_TransTime
ON dbo.transactions (trans_time, SiteID)
INCLUDE (transactionID,siteID,areacode,gamename,sitename)
GO
CREATE NONCLUSTERED INDEX idx_Dongle_TransactionID
ON dbo.dongle (transactionID)
INCLUDE ([current])
GO
CREATE NONCLUSTERED INDEX idx_longterm_TransactionID
ON dbo.longterm (transactionID)
INCLUDE(meter1, meter2, meter3, meter4, meter5)
GO
CREATE NONCLUSTERED INDEX idx_accounts_TransactionID
ON accounts (transactionID)
INCLUDE (vtp, cashin, totalwon, ticketout)
GO
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
June 23, 2015 at 8:10 am
wow thanks for this, adding these, will not effect the table data will it, the last thing i need is to mess up the database lol
June 23, 2015 at 8:15 am
Nope, they're extra indexes, they won't mangle, change or otherwise affect the data.
They may take some time to run, so make sure that you run them during a maintenance window or other scheduled downtime.
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
June 23, 2015 at 8:19 am
The first one
CREATE NONCLUSTERED INDEX idx_Transactions_TransTime
ON dbo.transactions (trans_time, SiteID)
INCLUDE (transactionID,siteID,areacode,gamename,sitename)
GO
is reporting
Msg 1909, Level 16, State 2, Line 1
Cannot use duplicate column names in index. Column name 'SiteID' listed more than once.
?
June 23, 2015 at 8:38 am
leetrueman (6/23/2015)
The first oneCREATE NONCLUSTERED INDEX idx_Transactions_TransTime
ON dbo.transactions (trans_time, SiteID)
INCLUDE (transactionID,siteID,areacode,gamename,sitename)
GO
is reporting
Msg 1909, Level 16, State 2, Line 1
Cannot use duplicate column names in index. Column name 'SiteID' listed more than once.
?
Try removing siteID from the INCLUDE clause:
CREATE NONCLUSTERED INDEX idx_Transactions_TransTime
ON dbo.transactions (trans_time, SiteID)
INCLUDE (transactionID, areacode, gamename, sitename)
GO
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply