Problem when large data

Last Post 15 Dec 2006 08:20 AM by JHunter. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
New Member
New Member

14 Dec 2006 10:21 PM
Hi to All

I have large database in sql server 2000 with tables and views on the server
around 80 to 100 mb amount of data.
when i retrieve the data from the server it is taking much time Ex: "select * from vwcases" its taking 10 to 12 minuts time.this is my database problem.If you know the solution for the reducing the time when retrieving the large amount of records.

you can see my view below.


create VIEW dbo.vwCases with schemabinding
SELECT [Case].Id AS Id, [Case].Created AS Created, [Case].SqFeets AS SqFeets, [Case].GraffitiType AS GraffitiClass,
when [Case].GraffitiType = 0 then 'None'
when [Case].GraffitiType = 1 then 'Graffiti'
when [Case].GraffitiType = 2 then 'Tag'
when [Case].GraffitiType = 4 then 'Unknown'
when [Case].GraffitiType = 8 then 'Not Defined'
when [Case].GraffitiType = 7 then 'All Defined'
when [Case].GraffitiType = 16 then 'Multiple Image'
else 'Error' end
) AS GraffitiClassName,
[Case].SurfaceType AS SurfaceType,
when [Case].SurfaceType = 0 then 'None'
when [Case].SurfaceType = 1 then 'Bridge'
when [Case].SurfaceType = 2 then 'Curb'
when [Case].SurfaceType = 4 then 'Electric Box'
when [Case].SurfaceType = 8 then 'Fence'
when [Case].SurfaceType = 16 then 'Fire Hydrant'
when [Case].SurfaceType = 32 then 'Garage Door'
when [Case].SurfaceType = 64 then 'Mailbox'
when [Case].SurfaceType = 128 then 'Pole'
when [Case].SurfaceType = 256 then 'Sidewalk'
when [Case].SurfaceType = 512 then 'Sign'
when [Case].SurfaceType = 1024 then 'Signal'
when [Case].SurfaceType = 2048 then 'Tree'
when [Case].SurfaceType = 4096 then 'Wall'
when [Case].SurfaceType = 8192 then 'Window'
when [Case].SurfaceType = 16384 then 'Other'
else 'Error' end
) AS SurfaceTypeName,
[Case].PhotoDateTime AS PhotoDateTime, [Case].Code AS Code, [Case].GpsLatitude AS GpsLatitude,
[Case].GpsLongitude AS GpsLongitude, [Case].ProcessedDateTime AS ProcessedDateTime, [Case].[Case] AS [Case],
[Case].RemovedDateTime AS RemovedDateTime,
[Case].CategoryType AS CategoryType,
(SELECT Graffiticategory.Category FROM GraffitiCategory WHERE GraffitiCategory.Id = [Case].CategoryType) AS CategoryTypeName,
[Case].ThreadType AS ThreadType,
[Case].Threatening AS Threatening,
(SELECT Team.Name FROM Team WHERE Team.Id = [Case].Threatening ) AS ThreateningName,
[Case].GraffitiClass AS GraffitiType,
when [Case].GraffitiClass = 0 then 'Publicity'
when [Case].GraffitiClass = 1 then 'Roll Call'
when [Case].GraffitiClass = 2 then 'Threat'
when [Case].GraffitiClass = 4 then 'Territorial'
when [Case].GraffitiClass = 8 then 'Sympathetic'
when [Case].GraffitiClass = 16 then 'Not Defined'
else 'Error' end
) AS GraffitiTypeName,
ISNULL([Case].AbandentCrew,'' ) AS AbandentCrew,
ISNULL(Address.StreetAddress, '') AS StreetAddress, Address.StreetAddress2 AS StreetAddress2, Address.Country AS Country,
Address.State AS State, Address.City AS City, Address.ZIP AS ZIP, Address.CrossStreet AS CrossStreet,
(select Team.[Id] from Team inner join CaseTeam ON Team.[Id]=CaseTeam.GangId where Team.TeamType = 4 and CaseTeam.CaseId = [Case].Id) AS CaseDepartment
Object ON [Case].Id = Object.Id LEFT OUTER JOIN
Address ON [Case].AddressId = Address.Id
WHERE (Object.Deleted IS NULL) AND [Case].GraffitiType < 9

New Member
New Member

15 Dec 2006 08:20 AM
From the view, it is clear there will be a full table scan on [case], and will probably be doing full scans on each of the correlated queries - once for every row in the [case] table. Look at the execution plan for the query to verify this.

Try modifying the query and implement the correlated queries as joins. And ensure your indexing is optimal...

New Member
New Member

12 Jan 2007 01:36 PM
Also you should put the hardwired CASE values into lookup tables
and join them.

Kalman Toth DB, DW & BI Architect, SQL Server Training
The Best SQL Server 2005 Training in the World
You are not authorized to post a reply.

Acceptable Use Policy