Thanks very much kccrosser.

My database doesnt have a huge volume of data. The database size is about 200MB.

I can see all queries running consistently under 2 seconds except in few cases it goes to 4-5 seconds.
When we tested the application with one user workflow, everything was absolutely fine. When we did the same test on Load Testing with 30 users. We could see performance issues. How do we diagonize this problem? What would be the possible solutions?

Also, the tips you mentioned are very helful.
I have used DISTINCT keyword as my joins in query produce duplicate records, may be a bad design, we cannot change the design now. Is there a way to get unique records without using DISTINCT?

I have a procedure which returns 7 result sets, that is, 7 select statements in the procedure which returns different result values. Even if each select query takes 1 sec, the procedure would take 7 seconds. Is there way to make this faster?

Thanks in advance…

Keep an eye out on Silos-Connect’s ActiveBase. Apparently it’ll support SQL Server soon and may be just what you need. It analyzes SQL queries and re-writes them to improve performance. It also has a security mechanism to block database attacks. Pretty cool.

You will need to post the actual queries (and some basic table schemas) for us to give any suggestions.

Common problems with large intermediate result sets are joins that are improperly formed. Including a table in the “from” and “select” clauses, but not joining in the “where” clause can produce a cartesian product, and a DISTINCT modifier then makes the huge intermediate result disappear.
It usually isn’t the table design – it is the query itself that is not optimized.

In any reasonably powerful server, unless a query is extremely complex or returns a LOT of data, I expect queries to run sub-second. When I see a query taking more than one second, it is worth looking at whether the query is not properly formed. We have individual tables in our terabyte-plus databases that are more than 200 MB, and the vast majority of query transactions are sub-second. Exceptions are queries that are intended to bring back large numbers of rows – typically report-type queries.

One easy way to create slow queries is to use Views. Many people create views to simplify the queries against the raw data, without considering that querying against a view may prevent the query optimizer from using existing indexes on the underlying tables. This happens especially when you start cascading views – defining a view which references another view. A view referencing another view is virtually guaranteed to cause table scans to occur.
If you need to use complex views, consider using materialized views, which essentially create real tables containing the view result set, and which can have indexes on the view columns.

In SQL Server, there is one other “interesting” feature worth mentioning… If you have read-only queries that really do need to take 2-5 seconds to execute, and there are concurrent insert/update transactions occurring on the database, consider adding the “with (NOLOCK)” or “with (READPAST)” hint to each table in the select statement. SQL Server defaults to “blocking” reads, which means that the queries can “stack up” when there are table updates occurring during the queries.
Purists may disagree with this, but if you have queries that are just used to display data (i.e., are NOT part of a “select … ; update …” transaction), adding the “with (NOLOCK)” hint after each table in the query can eliminate a lot of lock contention, making all the queries run faster.

Example:
select * from mytable where <condition>; — this will block if there are any updates happening to the table
select * from mytable with (NOLOCK) where <condition>; — this will NOT block

The difference is that the second version can return information that isn’t necessarily perfectly accurate as of that exact instant in time (a record in the result set may be in the middle of some other transaction). However, in virtually all cases, the results are essentially identical to what you would have gotten had you run the query a second earlier or a second later.

If you are seeing the performance degrade linearly (or worse) as you add concurrent query users, then I would immediately suggest adding the “with (NOLOCK)” hint and see if you see an immediate improvement. If a query takes T seconds to execute, running it N times concurrently should NOT take T*N seconds – it should normally take less time, unless the queries are blocking each other.
This is due to the fact that the database will be caching the row blocks fetched by the queries and often the other query instances will be accessing some of the same row blocks, thus they will not have to execute as many physical reads.

Thanks for the response kccrosser and Kevin.

Here is the procedure that display 9 resultsets.

declare @parentUniqueResourceId int, @IsNewTicket bit, @CultureName nvarchar(10)
set @parentUniqueResourceId = 102798
set @IsNewTicket= 0
set @CultureName=N’en-US’

DECLARE @level int
DECLARE @newParentId int
DECLARE @dataListItem int

if object_id(‘tempdb..#stack’) is not null
drop table #stack
if object_id(‘tempdb..#allChildrens’) is not null
drop table #allChildrens
CREATE TABLE #stack (item int, level int)
CREATE TABLE #allChildrens (childeren int)

INSERT INTO #stack VALUES (@parentUniqueResourceId, 1)

–print ‘entering procedure’

SET @level =1

WHILE @level > 0
BEGIN
IF (EXISTS (SELECT * FROM #stack WHERE level = @level) and @level<10)
BEGIN

SELECT @parentUniqueResourceId = max(item)
FROM #stack
WHERE level = @level

IF (@IsNewTicket = 1)
BEGIN
set @newParentId = (
SELECT
max(TicketResources.UniqueResourceId)
FROM
TicketResources (nolock)
WHERE
OriginalResourceId=(SELECT OriginalResourceId FROM TicketResources (nolock) WHERE UniqueResourceId=@parentUniqueResourceId and Enabled = 1 )
)
END
ELSE
BEGIN
set @newParentId =@parentUniqueResourceId
END

INSERT INTO #allChildrens(childeren) VALUES(@newParentId)

DELETE FROM #stack
WHERE level = @level
AND item = @parentUniqueResourceId

INSERT #stack
SELECT childUniqueResourceId, @level + 1
FROM TicketResourcesComponentList (nolock)
WHERE parentUniqueResourceId = @parentUniqueResourceId
IF @@ROWCOUNT > 0
SELECT @level = @level + 1
END
ELSE
SELECT @level = @level – 1
END — WHILE

– Get Parent child Relationship in the table
SELECT parentuniqueResourceid as parent, childuniqueresourceid as children, ticketResourceTypeEnumId , TicketResourcesComponentListId, ChildresourceOrder
FROM
TicketResourcesComponentList (nolock)
Join
TicketResources (nolock) on parentuniqueResourceid = Uniqueresourceid
WHERE
ParentUniqueResourceid in (SELECT childeren FROM #allChildrens)
ORDER BY
ParentUniqueResourceid,ChildresourceOrder

– Get all properties for all the resources
SELECT TicketResourcesProperties.*, dbo.fn_GetLocalizedString(LocalizedStringValueId, @CultureName) AS LocalizedStringValue
FROM
TicketResourcesProperties (nolock)
WHERE
UniqueResourceId in (SELECT childeren FROM #allChildrens)
ORDER BY
UniqueResourceId

– Get all resources from resources table required for supplied resource ID
select A.*,B.*, dbo.fn_GetLocalizedString(A.DescriptionId, @CultureName) AS Description ,
dbo.fn_GetLocalizedString(PrintedProductShortDescriptionId, @CultureName) AS PrintedProductShortDescription,
dbo.fn_GetLocalizedString(PrintedProductDetailedDescriptionLeftId, @CultureName) AS PrintedProductDetailedDescriptionLeft,
dbo.fn_GetLocalizedString(PrintedProductDetailedDescriptionRightId, @CultureName) AS PrintedProductDetailedDescriptionRight
From
TicketResources A (nolock)
LEFT OUTER JOIN TicketResourcesImageSets B (nolock) ON A.TicketResourcesImageSetId = B.TicketResourcesImageSetId
WHERE
UniqueResourceId in (SELECT childeren FROM #allChildrens)
ORDER BY
UniqueResourceId

SELECT
a.UniqueResourceId, mc.MediaCatalogId as ItemId, mc.TabCount, mc.MediaImageUrl,
dbo.fn_GetLocalizedString(mc.DescriptionId, @CultureName) AS LocalizedStringValue,

– recommended setting
a.IsRecommended,

– color data
mc.MediaColorId,
dbo.fn_GetLocalizedString(mco.DescriptionId, @CultureName) AS ColorDescription,

– size data
mc.MediaSizeId,
dbo.fn_GetLocalizedString(ms.DescriptionId, @CultureName) AS SizeDescription,

– weight data
mc.MediaWeightId,
dbo.fn_GetLocalizedString(mw.DescriptionId, @CultureName) AS WeightDescription,

– weight data
mc.MediaTypeId,
dbo.fn_GetLocalizedString(mt.DescriptionId, @CultureName) AS TypeDescription,
a.IntValue

FROM
TicketResourcesProperties a (nolock )
inner join MediaCatalog mc (nolock) on a.MediaCatalogId = mc.MediaCatalogId
inner join MediaSizes ms (nolock) on mc.MediaSizeId = ms.MediaSizeId
inner join MediaColors mco (nolock) on mc.MediaColorId = mco.MediaColorId
inner join MediaWeights mw (nolock) on mc.MediaWeightId = mw.MediaWeightId
inner join MediaTypes mt (nolock) on mc.MediaTypeId = mt.MediaTypeId
WHERE
a.UniqueResourceId in (SELECT childeren FROM #allChildrens)
ORDER BY
LocalizedStringValue

SELECT
a.UniqueResourceId,
c.featureid as ItemId,
dbo.fn_GetLocalizedString(c.DescriptionId, @CultureName) AS LocalizedStringValue,
dbo.fn_GetLocalizedString(c.SecondaryDescriptionId , @CultureName) AS SecondaryLocalizedStringValue,
c.ImageUrl,
–c.ImageUrlOn, commenting this code right now because we are removing ImageUrlOn, and ImageUrl sum from the Features table. Its temporary solution
–c.ImageUrlSum,
c.ImageUrl as PrimaryImage,
c.ImageUrl as SecondaryImage,
a.IsRecommended ,
c.DefaultUICategoryId,
dbo.fn_GetLocalizedString(d.DescriptionId , @CultureName) AS DefaultUICategory,
dbo.fn_GetLocalizedString(c.SecondaryDescriptionId , @CultureName) AS SecondaryLocalizedStringValue,
dbo.fn_GetLocalizedString(e.DescriptionId , @CultureName) AS ComplexPrimaryList, e.ComplexFeatureId,
c.ImageId
FROM
TicketResourcesProperties a (nolock)
INNER JOIN Features c (nolock) ON c.FeatureId = a.FeatureId
INNER JOIN dbo.UIFeatureCategories d (nolock) ON c.DefaultUICategoryId = d.UIFeatureCategoryId
LEFT OUTER JOIN dbo.ComplexFeatures e (nolock) ON c.ComplexFeatureId = e.ComplexFeatureId
WHERE
a.UniqueResourceId in (SELECT childeren FROM #allChildrens)
ORDER BY
a.UniqueResourceId, c.PerformsFeature, LocalizedStringValue — Order them by setting No value as first and than by their description

if object_id(‘tempdb..#tmp_media_catalog_details’) is not null
drop table #tmp_media_catalog_details
SELECT
a.UniqueResourceId , mc.MediaCatalogId as ItemId, mc.TabCount, mc.MediaImageUrl,
dbo.fn_GetLocalizedString(mc.DescriptionId, @CultureName) AS LocalizedStringValue,

– recommended setting
a.IsRecommended,

– color data
mc.MediaColorId,
dbo.fn_GetLocalizedString(mco.DescriptionId, @CultureName) AS ColorDescription,

– size data
mc.MediaSizeId,
dbo.fn_GetLocalizedString(ms.DescriptionId, @CultureName) AS SizeDescription,

– weight data
mc.MediaWeightId,
dbo.fn_GetLocalizedString(mw.DescriptionId, @CultureName) AS WeightDescription,

– weight data
mc.MediaTypeId,
dbo.fn_GetLocalizedString(mt.DescriptionId, @CultureName) AS TypeDescription,
a.IntValue
into #tmp_media_catalog_details
FROM
TicketResourcesProperties a (nolock)
inner join MediaCatalog mc (nolock) on a.MediaCatalogId = mc.MediaCatalogId
left outer join MediaSizes ms (nolock) on mc.MediaSizeId = ms.MediaSizeId
left outer join MediaColors mco (nolock) on mc.MediaColorId = mco.MediaColorId
left outer join MediaWeights mw (nolock) on mc.MediaWeightId = mw.MediaWeightId
left outer join MediaTypes mt (nolock) on mc.MediaTypeId = mt.MediaTypeId
WHERE
a.UniqueResourceId in (SELECT childeren FROM #allChildrens)

– Color
select distinct MediaColorId, ColorDescription from #tmp_media_catalog_details where MediaColorId is not null order by ColorDescription
– Size
select distinct MediaSizeId, SizeDescription from #tmp_media_catalog_details where MediaSizeId is not null order by SizeDescription
– Weight
select distinct MediaWeightId, WeightDescription from #tmp_media_catalog_details where MediaWeightId is not null order by WeightDescription
– Type
select distinct MediaTypeId, TypeDescription from #tmp_media_catalog_details where MediaTypeId is not null order by TypeDescription

Return

– Features
/*
SELECT a.UniqueResourceId , c.featureid as ItemId, dbo.fn_GetLocalizedString(c.DescriptionId, @CultureName) AS LocalizedStringValue,
dbo.fn_GetLocalizedString(c.SecondaryDescriptionId , @CultureName) AS SecondaryLocalizedStringValue,
c.ImageUrl,
– c.ImageUrlOn, commenting this code right now because we are removing ImageUrlOn, and ImageUrl sum from the Features table. Its temporary solution
– c.ImageUrlSum,
c.ImageUrl as PrimaryImage,
c.ImageUrl as SecondaryImage,
a.IsRecommended , c.DefaultUICategoryId,
dbo.fn_GetLocalizedString(d.DescriptionId , @CultureName) AS DefaultUICategory,
dbo.fn_GetLocalizedString(c.SecondaryDescriptionId , @CultureName) AS SecondaryLocalizedStringValue

FROM
TicketResourcesProperties a ,FEATURES c, UIFeatureCategories d, ComplexFeatures e
WHERE
UniqueResourceId in (SELECT distinct childeren FROM #allChildrens)
AND
c.featureid = a.featureid
AND
d.UIFeatureCategoryId = c.DefaultUICategoryId

ORDER BY a.UniqueResourceId,c.PerformsFeature,LocalizedStringValue — Order them by setting No value as first and than by their description
*/

SELECT
a.UniqueResourceId,
c.featureid as ItemId,
dbo.fn_GetLocalizedString(c.DescriptionId, @CultureName) AS LocalizedStringValue,
dbo.fn_GetLocalizedString(c.SecondaryDescriptionId , @CultureName) AS SecondaryLocalizedStringValue,
c.ImageUrl,
–c.ImageUrlOn, commenting this code right now because we are removing ImageUrlOn, and ImageUrl sum from the Features table. Its temporary solution
–c.ImageUrlSum,
c.ImageUrl as PrimaryImage,
c.ImageUrl as SecondaryImage,
a.IsRecommended ,
c.DefaultUICategoryId,
dbo.fn_GetLocalizedString(d.DescriptionId , @CultureName) AS DefaultUICategory,
dbo.fn_GetLocalizedString(c.SecondaryDescriptionId , @CultureName) AS SecondaryLocalizedStringValue,
dbo.fn_GetLocalizedString(e.DescriptionId , @CultureName) AS ComplexPrimaryList, e.ComplexFeatureId,
c.ImageId
FROM
TicketResourcesProperties a (nolock)
INNER JOIN Features c (nolock) ON c.FeatureId = a.FeatureId
INNER JOIN dbo.UIFeatureCategories d (nolock) ON c.DefaultUICategoryId = d.UIFeatureCategoryId
LEFT OUTER JOIN dbo.ComplexFeatures e (nolock) ON c.ComplexFeatureId = e.ComplexFeatureId
WHERE
a.UniqueResourceId in (SELECT childeren FROM #allChildrens)
ORDER BY
a.UniqueResourceId, c.PerformsFeature, LocalizedStringValue — Order them by setting No value as first and than by their description

if object_id(‘tempdb..#tmp_media_catalog_details’) is not null
drop table #tmp_media_catalog_details

SELECT
a.UniqueResourceId , mc.MediaCatalogId as ItemId, mc.TabCount, mc.MediaImageUrl,
dbo.fn_GetLocalizedString(mc.DescriptionId, @CultureName) AS LocalizedStringValue,

– recommended setting
a.IsRecommended,

– color data
mc.MediaColorId,
dbo.fn_GetLocalizedString(mco.DescriptionId, @CultureName) AS ColorDescription,

– size data
mc.MediaSizeId,
dbo.fn_GetLocalizedString(ms.DescriptionId, @CultureName) AS SizeDescription,

– weight data
mc.MediaWeightId,
dbo.fn_GetLocalizedString(mw.DescriptionId, @CultureName) AS WeightDescription,

– weight data
mc.MediaTypeId,
dbo.fn_GetLocalizedString(mt.DescriptionId, @CultureName) AS TypeDescription,
a.IntValue
into #tmp_media_catalog_details
FROM
TicketResourcesProperties a (nolock, index=idx_MediaCatalogId_TicketResourcesProperties, index=idx_UniqueResourceID_TicketResourcesProperties)
inner join MediaCatalog mc (nolock) on a.MediaCatalogId = mc.MediaCatalogId
left outer join MediaSizes ms (nolock) on mc.MediaSizeId = ms.MediaSizeId
left outer join MediaColors mco (nolock) on mc.MediaColorId = mco.MediaColorId
left outer join MediaWeights mw (nolock) on mc.MediaWeightId = mw.MediaWeightId
left outer join MediaTypes mt (nolock) on mc.MediaTypeId = mt.MediaTypeId
WHERE
a.UniqueResourceId in (SELECT childeren FROM #allChildrens)

– Color
select distinct MediaColorId, ColorDescription from #tmp_media_catalog_details where MediaColorId is not null order by ColorDescription
– Size
select distinct MediaSizeId, SizeDescription from #tmp_media_catalog_details where MediaSizeId is not null order by SizeDescription
– Weight
select distinct MediaWeightId, WeightDescription from #tmp_media_catalog_details where MediaWeightId is not null order by WeightDescription
– Type
select distinct MediaTypeId, TypeDescription from #tmp_media_catalog_details where MediaTypeId is not null order by TypeDescription

RETURN

Maximum number records in a table is 5000, and most of them are less than 5000. The above query runs in 3 secs for the first time and 1 sec for the next consecutive run. Can we do something more to make it run less than a second?

Here is the result of the query..

Result-1
parent children ticketResourceTypeEnumId TicketResourcesComponentListId ChildresourceOrder
102796 102797 88 102446 0
102797 102798 98 102447 0
102797 102803 98 102452 1
102797 102805 98 102454 2

Result-2
TicketResourcesPropertiesId UniqueResourceId ResourcePropertyEnumValueId IntValue BoolValue LocalizedStringValueId FeatureId MediaCatalogId IsRecommended LocalizedStringValue
106535 102796 336 NULL 0 NULL NULL NULL 0 NULL
106536 102796 300 NULL 1 NULL NULL NULL 0 NULL

Result-3
UniqueResourceId OriginalResourceId Enabled TicketResourceTypeEnumId CreatedByUser DescriptionId PrintedProductShortDescriptionId ControlToDisplayEnumId TicketResourcesImageSetId PrintedProductDetailedDescriptionLeftId PrintedProductDetailedDescriptionRightId PrintedProductDescriptionFormat TicketResourcesImageSetId DescriptionId ImageUrl MiniImageUrl SuperGraphicImageUrl Description PrintedProductShortDescription PrintedProductDetailedDescriptionLeft PrintedProductDetailedDescriptionRight
102796 102796 1 88 administrator 104893 104894 110 1 104895 104896 0 1 865 ~ImagesticketingImages_ProductsDSF_Icons_PrintServicesDSF_Icon_PS_Copies_65.gif NULL ~ImagesticketingImages_ProductsDSF_Images_SupergraphicsDSF_BigImage_Copies.gif LR_Static Basic duplication products
102797 102797 1 98 administrator NULL NULL 111 NULL NULL NULL -1 NULL NULL NULL NULL NULL NULL NULL NULL NULL
102798 102798 1 97 administrator 104897 NULL 112 NULL NULL NULL -1 NULL NULL NULL NULL NULL Print Options NULL NULL NULL

–Result-4
UniqueResourceId ItemId TabCount MediaImageUrl LocalizedStringValue IsRecommended MediaColorId ColorDescription MediaSizeId SizeDescription MediaWeightId WeightDescription MediaTypeId TypeDescription IntValue
102799 100014 0 NULL 100 LB GLOSS COVER 1 4 White 22 12″ x 18″ 24 Cover (100 pound)/271 gsm 20 Paper (Generic) NULL
102799 100009 0 NULL 100 LB GLOSS TEXT 0 4 White 22 12″ x 18″ 27 Bristol (100 pound)/219 gsm 20 Paper (Generic) NULL
102799 100011 0 NULL 100 LB MATTE COVER 0 4 White 22 12″ x 18″ 23 Cover (90 pound)/243 gsm 20 Paper (Generic) NULL

–Result-5
UniqueResourceId ItemId LocalizedStringValue SecondaryLocalizedStringValue ImageUrl PrimaryImage SecondaryImage IsRecommended DefaultUICategoryId DefaultUICategory SecondaryLocalizedStringValue ComplexPrimaryList ComplexFeatureId ImageId
102800 186 Do Not Collate NULL ~ImagesticketingIconsFeatures186.gif ~ImagesticketingIconsFeatures186.gif ~ImagesticketingIconsFeatures186.gif 0 60 Collate NULL NULL NULL NULL
102800 185 Collate into Sets NULL ~ImagesticketingIconsFeatures185.gif ~ImagesticketingIconsFeatures185.gif ~ImagesticketingIconsFeatures185.gif 1 60 Collate NULL NULL NULL NULL

–Result-6
MediaColorId ColorDescription
4 White

–Result-7
MediaSizeId SizeDescription
22 12″ x 18″

–Result-8
MediaWeightId WeightDescription
27 Bristol (100 pound)/219 gsm
24 Cover (100 pound)/271 gsm
23 Cover (90 pound)/243 gsm

–Result-9
MediaTypeId TypeDescription
20 Paper (Generic)

Thanks in advance…