Stored Procedure refactoring

user_545877 0
Viewed 66

I am looking at refactoring this stored procedure as it seems that it is causing a timeout on the Azure DB using 100% of DTU capacity.

Does anyone have any suggestions how I could improve the SQL used in my stored proc?

Thanks

declare @value bigint
declare @secondValue bigint 

select @value = count(*) from [dbo].[myTable]
where ([Name] = 'something') AND ([Type] = 'paid') AND profilestatus = 'something'
and ExpiresOn >= CURRENT_TIMESTAMP

select @trialing = count(*) from [dbo].[myTable]
where ([Name] = 'something') AND ([Type] = 'avalue') AND profilestatus = 'something'
and ExpiresOn >= CURRENT_TIMESTAMP

select @value as firstValue, @secondValue as secondValue

Client Stats:

enter image description here

stored-procedures sql-server sql
2 Answers

I see you want to fetch two counts - one for each of two values of [Type], in this case a group by should improve your performance significantly:

select [Type], count(*) AS cnt
from [dbo].[myTable]
where ([Name] = 'something') AND ([Type] IN ('paid', 'avalue')) AND profilestatus = 'something' AND ExpiresOn >= CURRENT_TIMESTAMP
group by [Type]

You can incorporate that query into your stored procedure expecting two records in the result set.

user_12445630

Your queries are simple and straight-forward. I see two things to review and consider.

First, how big is the table? Are there 100, 100,000, or 10eBIG rows? How much of this data has to be read in order to determine your results?

Probably a lot, which gets to the second issue: what indexes have been built on this table? If you have 1,000,000 rows, but only 23 of them have Name = 'something', then an index on table Name would greatly improve performance. Conversely, if 90% of the rows are 'something', an index on that column would not help with this query. By and large, the purpose of an index is to help filter how much data has to be physically read when executing a query.

Things start to get complex with your filtering on four columns, as only one index will be used. Can a single index be built that would optimize these queries? It depends on what your data looks like. For further reference, you should review the topics of “sql covering indexes” and “sql cardinality”; google searches on these will find many, many articles and discussions on these important topics.

user_74910
Related