Searching large tables - SQL Server Forums

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

SQL Server 2005 Forums

SQL Server Administration (2005)

Searching large tables

Author

Topic

ih8grep
Starting Member

3 Posts

Posted - 2009-08-18 : 09:01:54

Running 2005 standard.

Are there any tricks for improving query performance on large tables? I read about indexing but the articles all said it's best to do on columns with unique numbers. My queries require sifting through fields with thousands of duplicate entries.

I'm storing my ISA server logs from 22 satellite offices in one large centralized database for the purpose of usage analysis and reporting. With 7 days of data the table is 6 gigs with 20 million records. I would like to keep 45 days. The size is not unexpected, and I have got lots of hard drive space for it.

What I do not have available is RAM. The DB is running on server 03 with just 1gb. I know it's going to take a performance hit, and I'm ok with that, it's just me using the data. Im often counting hits for a particular clientusername, or summing bytes received on a specific remotehost. Can I index clientusername (varchar32) and remotehost (varchar128)? Is there some other way to help speed up queries?

Thanks in advance!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-08-18 : 12:45:26

Show us your queries so that we can help you select the right index.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

ih8grep
Starting Member

3 Posts

Posted - 2009-08-18 : 22:45:38

Thanks tkizer. I included the table structure as well. It came straight from the ISA 2000 manual. I only added sitecode and urihost (which is extracted by my processing script before being inserted into the database).

yesterdays top 40:
SELECT TOP (40) ROUND(SUM(bytesrecvd / 1000000), 3) AS bw, urihost
FROM dbo.isalog_data
WHERE (resultcode <> 407) AND (logdate = DATEADD(dd, - 1, DATEDIFF(dd, 0, GETDATE())))
GROUP BY urihost
ORDER BY bw DESC

top users by office (sitecode) and website
SELECT ROUND(SUM(bytesrecvd / 1000000), 3) AS bw, clientusername, urihost, sitecode
FROM dbo.isalog_data
WHERE (resultcode > 199) AND (resultcode < 300) AND (clientusername NOT LIKE '%mcafeeupdate%')
GROUP BY clientusername, urihost, sitecode
HAVING (ROUND(SUM(bytesrecvd / 1000000), 3) > .5)

daily totals
SELECT ROUND(SUM(bytesrecvd / 1000000), 3) AS bw, sitecode, logdate
FROM dbo.isalog_data
GROUP BY sitecode, logdate

table structure
USE [isalogs_fleet]
GO
/****** Object: Table [dbo].[isalog_data] Script Date: 08/18/2009 22:44:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[isalog_data](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[sitecode] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[clientip] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[clientusername] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[clientagent] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[logdate] [datetime] NULL,
[logtime] [datetime] NULL,
[servername] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[referredserver] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[desthost] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[destip] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[destport] [int] NULL,
[processingtime] [int] NULL,
[bytessent] [float] NULL,
[bytesrecvd] [float] NULL,
[protocol] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[operation] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[uri] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[objectsource] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[resultcode] [smallint] NULL,
[urihost] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

pkuchaliya
Starting Member

49 Posts

Posted - 2009-08-19 : 07:13:23

hi,
Follow This link
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762

pankaj

amrosalah
Starting Member

6 Posts

Posted - 2009-08-19 : 16:25:06

You need to move from OLTP to OLAP . You should consider using SQL SERVER ANALYSIS Services as a solution for your problem , since you are importing millions of records , not adding a few every day , your database is a good candidate for creating a data mart and creating a cube on it .
Once you have a cube ready you can start doing queries on it and will be very fast .

http://www.freesqltools.info

ih8grep
Starting Member

3 Posts

Posted - 2009-08-20 : 19:56:30

pkuchaliya - thanks for the link, I'll keep it handy. Right now my log file is only 10 megs. Will trimming it help speed up my queries?

amrosalah - wow, looks like I have some reading to do. In the mean time, is there any way I can trade hard drive space (I have lots) for speed with these queries?

IncisiveOne
Starting Member

36 Posts

Posted - 2009-08-24 : 12:53:51

Hard drive = storage
Processor = speed
RAM = Processor capacity
Whenever you do fetch all that from storage, you have to churn it through the processor, of which the capacity is RAM. Regardless of whatever you come up with, the 1gb RAM is the limiting or governing factor, and you should be designing for that, noy denying/avoiding that. That's what a cube is designed as: a large data fooprint to be churned using a small processor with limited RAM.

Now if you had some of the features that the big end of town has, there are intermediate steps between what you have and a data cube. But not here.

Anything worth doing, is worth doing right.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources