Problem when large data

Last Post 15 Dec 2006 08:20 AM by JHunter. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
balaMunugoti
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.


View:
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON
GO

create VIEW dbo.vwCases with schemabinding
AS
SELECT [Case].Id AS Id, [Case].Created AS Created, [Case].SqFeets AS SqFeets, [Case].GraffitiType AS GraffitiClass,
(case
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,
(case
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,
(case
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
FROM [Case] INNER JOIN
Object ON [Case].Id = Object.Id LEFT OUTER JOIN
Address ON [Case].AddressId = Address.Id
WHERE (Object.Deleted IS NULL) AND [Case].GraffitiType < 9

GO
SET QUOTED_I
JHunter
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...

Jamie
SQLUSA
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
URL: http://www.sqlusa.com/order2005grandprix
The Best SQL Server 2005 Training in the World
You are not authorized to post a reply.

Acceptable Use Policy