app Performance question stored proc

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

General SQL Server Forums

New to SQL Server Programming

app Performance question stored proc

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-03-19 : 22:47:20

I have a stored proc that takes some time to run, 11-12 seconds. the app takes the user id and gets the customers or the user. then the stored proc 'gettransactions' which you can see here: http://pastebin.com/JVnkwCEt
gets all the transactions for the users customer. My understanding is that by querying the table transactions, it has to read thru a 10mb of data (compressed). This data is not real time it is refreshed each morning, to our reporting server, although this may become realtime at some point. suppose I would have a job that would during the refresh, take each customer we have, and make small tables, e.g. transactions_companyABC, transactions_companyDEF, etc. and then the app just has to locate the right table to read, not too difficult and then we are reading less rows and performance should soar.

Is this a good idea, or is there a much better way to improve this performace.

I am aware that the select where clause is somewhat problematic. because where clause is on CustomerID and DateCreated.
Both these column have duplicate values in thousands.

There is no index on these columns, just on TransactionId which isn't being used in the where clause. (I hve started to rebuild the indec ad hoc and this saves a second).

So either we can add a derived column of type DateTime and which we’ll do is to add the TransactionId (take it as milliSeconds)
to DateDateCreated column. Then we can simply put where clause on that computed column and make it as Clustered Index.

But still, I am thinking my custom tables creating would also help.

I would appreciate experienced opinions as to how to best go about improving performance, we have measured the stored proc at 12 seconds in the app.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-03-20 : 11:10:47

Without a useful index, the query will never perform well.
Please post the table definitions and the query here (just paste them into your reply), as well as some indication how many rows there are in total and how many the query affects

As for separate tables, terrible idea.

--
Gail Shaw
SQL Server MVP

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-03-20 : 21:49:45

transactions table:
TransactionId (PK, Int, NotNull)
CustomerId (FK, Int, Notnull)
DepartmentId (FK, Int, Null)
ItemId (FK, Int, Null)
CategoryId (FK, Int, Null)
Quantity (Numeric, 19,2, Null)
Cost (Numeric, 19,4, Null)
DateCreated (Datetime, Notnull)
InvoiceNumber (Char, 20, Null)
DataSource (FK, Char(10), Notnull)
DataSourceId (char, 20, Null)
ImportId (FK, Int, Null)
LastUpdate (datetime, notnull)
ItemMappingId (FK, Int. Null)

Query:
USE [SpendAl]
GO
/****** Object: StoredProcedure [dbo].[GetTransactions] Script Date: 03/20/2011 21:48:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- drop procedure GetTransactions
ALTER PROCEDURE [dbo].[GetTransactions]

@CustomerId INT = NULL

AS

BEGIN

INSERT INTO App_Log
(Info, Start, EndDate)
VALUES
('GetTransactions', getDate(), null )
Declare @logId int
SET @logId = @@IDENTITY

SET NOCOUNT ON;

SELECT LTRIM(RTRIM(ISNULL(d.DepartmentName, ''))) AS DepartmentName
,d.DepartmentId AS DepartmentId
,ISNULL(t.Cost, 0) AS Expense
,ISNULL(t.Quantity , 0)AS Quantity
,LTRIM(RTRIM(ISNULL(c.CustomerName, ''))) AS CUSTNAME
,LTRIM(RTRIM(ISNULL(i.ItemName, ''))) AS Description
,t.DateCreated AS DateCreated
,LTRIM(RTRIM(ISNULL(ct.CategoryLongName, ''))) AS CategoryName
,LTRIM(RTRIM(ISNULL(ct.CategoryName, ''))) AS USCATVAL
--,'00000' AS Budget
,LTRIM(RTRIM(ISNULL(t.InvoiceNumber,''))) as InvoiceNum
FROM Transactions t
LEFT JOIN Departments d ON t.DepartmentId = d.DepartmentId
LEFT JOIN Customers c ON t.CustomerId = c.CustomerId
LEFT JOIN Categories ct ON t.CategoryId = ct.CategoryId
LEFT JOIN Items i ON t.ItemId = i.ItemId
WHERE DateCreated >= DATEADD(YYYY, - 2, GETDATE())
AND DateCreated <= GETDATE()
AND t.CustomerId = @CustomerId
ORDER BY DateCreated DESC

UPDATE App_Log
SET EndDate = GETDATE()
where ID = @logId

END

--exec GetTransactions

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-03-21 : 05:31:33

What indexes exist on that table?

How many rows would that select return, how many rows in Transactions?

--
Gail Shaw
SQL Server MVP

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-03-21 : 10:51:53

INdexes:
IX_Transactions (non-unique, non-clustered)
PK_Transactions (Clustered)

Rows typical customer returns about 4400 rows.
total table transaction has about 740,000 rows.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-03-21 : 13:44:30

And what is the definition of IX_Transactions? The name doesn't exactly say anything useful.

--
Gail Shaw
SQL Server MVP

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-03-21 : 15:22:49

upon right click i see the def. is>
Datecreated, with these values: ascend, datetime,size = 8, identity = no, allow nulls = no

also the pk_transactions has the value for Transactionid >
ascend, int, size = 4, id=yes, allow nulls = no

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-03-21 : 17:26:06

Ok, that's useless for this query

Try the following index (column order is important)

Customer, DateCreated, DepartmentId, CategoryId, ItemId INCLUDE (Quantity, Cost)

It is a fairly index, but that's a small table so shouldn't have too much of an effect.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources