Large table, really slow queries

I'm working with a table with about 60 million records. This monster is growing every minute of the day as well, by 200,000 - 300,000 records/day. It's 11 columns wide, and has one index on a datetime column. My task is to create some custom reports based on three of these columns, including the datetime one.

The problem is response time. Any query executed on this table takes forever--anywhere between 30 seconds and 4 minutes. Queries such as this one below, as simple as it is, can take a minute or more:

select
count(dt_date) as Searches
from
SearchRecords
where
datediff(day,getdate(),dt_date)=0

As the table gets larger and large, the response time is going to get worse and worse. Long story short, what are my options to get the speed of queries down to just a few seconds with a table this big? So far the best I can come up with is index any other appropriate columns (of which there is one for sure, maybe two).

may be you can try table partitioning in sql 2005..............but i am not sure about this....refer,

http://dumthata.com/sqlchn/articles/dpart.htm

|||

You should rewrite the query to remove the DATEDIFF function from the WHERE clause. This causes SQL Server to scan the table.

It is better to use WHERE dt_date BETWEEN '2007-07-26' AND '2007-07-27' for example.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Wow, what a world of difference it makes if I don't use datediff()!

Is there a general rule that will tell me whether the functions I use in my WHERE clause will cause a table scan versus index look-up? I'm not a DBA, just a .NET developer working on the back-end at the moment.

Immensely helpful, thank-you.
|||

Not just a rule of thumb, but an absolute no-no.

If you 'wrap' a field with a function, the query processor MUST apply that function to every row in the table (or index) to determine if the 'functionalized' value meets the criteria. As a result, the query will NOT efficiently utilize any indexing. It will require at least an index scan, and perhaps even a scan of the entire table.

If in a query, all rows newer than seven days ago are sought, you can either add seven days to the stored value and compare to the current datetime, or you can subract seven days from the current datetime and compare to the stored value.

For example, in this WHERE clause, :

WHERE dateadd( day, 7, MyColumn ) >= getdate()

Indexing on MyColumn cannot be properly used, the query will need to do an index scan (if MyColumn is indexed), and a table scan otherwise.

However you can easily use a function on the non-field side of the equality and still use indexes.

WHERE MyColumn <= dateadd( day, -7, getdate() )

Large Log Files

I have several log files, .ldf, that keep growing and growing. I have a
database maintenance plan for these in which I am backing up the transaction
logs. How would I prevent these logs from continually getting larger? I
have SQL 2000 running on a Windows 2003 standard server.
I have other databases that the .ldf files don't grow...
Any help would be appreciated.
Thank you.
--
Mike StevensAlso, do you have some old open transaction? (DBCC OPENTRAN)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Stevens" <Stevens@.discussions.microsoft.com> wrote in message
news:94506F40-C755-43D8-9256-96EEE68E4211@.microsoft.com...
>I have several log files, .ldf, that keep growing and growing. I have a
> database maintenance plan for these in which I am backing up the transaction
> logs. How would I prevent these logs from continually getting larger? I
> have SQL 2000 running on a Windows 2003 standard server.
> I have other databases that the .ldf files don't grow...
> Any help would be appreciated.
> Thank you.
> --
> Mike Stevens|||Are you 100% certain that you do perform log backup on this database? What does the virtual file
structure look like? (See http://www.karaszi.com/SQLServer/info_dont_shrink.asp)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Stevens" <Stevens@.discussions.microsoft.com> wrote in message
news:94506F40-C755-43D8-9256-96EEE68E4211@.microsoft.com...
>I have several log files, .ldf, that keep growing and growing. I have a
> database maintenance plan for these in which I am backing up the transaction
> logs. How would I prevent these logs from continually getting larger? I
> have SQL 2000 running on a Windows 2003 standard server.
> I have other databases that the .ldf files don't grow...
> Any help would be appreciated.
> Thank you.
> --
> Mike Stevens|||As I said, I have a database maintenance plan for these in which I am backing
up the transaction logs. They create a .trn file. The .trn file is
considerable smaller than the .ldf. The .ldf file just keeps growing.
Mike Stevens
"Tibor Karaszi" wrote:
> Are you 100% certain that you do perform log backup on this database? What does the virtual file
> structure look like? (See http://www.karaszi.com/SQLServer/info_dont_shrink.asp)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Stevens" <Stevens@.discussions.microsoft.com> wrote in message
> news:94506F40-C755-43D8-9256-96EEE68E4211@.microsoft.com...
> >I have several log files, .ldf, that keep growing and growing. I have a
> > database maintenance plan for these in which I am backing up the transaction
> > logs. How would I prevent these logs from continually getting larger? I
> > have SQL 2000 running on a Windows 2003 standard server.
> >
> > I have other databases that the .ldf files don't grow...
> >
> > Any help would be appreciated.
> >
> > Thank you.
> > --
> > Mike Stevens
>|||On Feb 9, 8:25 am, Stevens <Stev...@.discussions.microsoft.com> wrote:
> I have several log files, .ldf, that keep growing and growing. I have a
> database maintenance plan for these in which I am backing up the transaction
> logs. How would I prevent these logs from continually getting larger? I
> have SQL 2000 running on a Windows 2003 standard server.
> I have other databases that the .ldf files don't grow...
> Any help would be appreciated.
> Thank you.
> --
> Mike Stevens
Is the database in question enabled for replication? Define "keeps
growing and growing". How fast is it growing? Does it grow every
day, or only on weekends? Are you running a reindex job or something
that might be producing large transactions?|||What about the virtual log file structure, and result from DBCC OPENTRAN?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Stevens" <Stevens@.discussions.microsoft.com> wrote in message
news:CA33D6C4-850B-4E77-AADB-11EF06A11BBA@.microsoft.com...
> As I said, I have a database maintenance plan for these in which I am backing
> up the transaction logs. They create a .trn file. The .trn file is
> considerable smaller than the .ldf. The .ldf file just keeps growing.
>
> --
> Mike Stevens
>
> "Tibor Karaszi" wrote:
>> Are you 100% certain that you do perform log backup on this database? What does the virtual file
>> structure look like? (See http://www.karaszi.com/SQLServer/info_dont_shrink.asp)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Stevens" <Stevens@.discussions.microsoft.com> wrote in message
>> news:94506F40-C755-43D8-9256-96EEE68E4211@.microsoft.com...
>> >I have several log files, .ldf, that keep growing and growing. I have a
>> > database maintenance plan for these in which I am backing up the transaction
>> > logs. How would I prevent these logs from continually getting larger? I
>> > have SQL 2000 running on a Windows 2003 standard server.
>> >
>> > I have other databases that the .ldf files don't grow...
>> >
>> > Any help would be appreciated.
>> >
>> > Thank you.
>> > --
>> > Mike Stevens
>>|||I'm quite a rookie at SQL. I'm not sure how to tell if the database is
enabled for replication. The .ldf file grows about 20-30 meg. a day. I
don't think I'm running any reindexing job.
Thanks for your help.
--
Mike Stevens
"Tracy McKibben" wrote:
> On Feb 9, 8:25 am, Stevens <Stev...@.discussions.microsoft.com> wrote:
> > I have several log files, .ldf, that keep growing and growing. I have a
> > database maintenance plan for these in which I am backing up the transaction
> > logs. How would I prevent these logs from continually getting larger? I
> > have SQL 2000 running on a Windows 2003 standard server.
> >
> > I have other databases that the .ldf files don't grow...
> >
> > Any help would be appreciated.
> >
> > Thank you.
> > --
> > Mike Stevens
> Is the database in question enabled for replication? Define "keeps
> growing and growing". How fast is it growing? Does it grow every
> day, or only on weekends? Are you running a reindex job or something
> that might be producing large transactions?
>

Large Log Files

I have several log files, .ldf, that keep growing and growing. I have a
database maintenance plan for these in which I am backing up the transaction
logs. How would I prevent these logs from continually getting larger? I
have SQL 2000 running on a Windows 2003 standard server.
I have other databases that the .ldf files don't grow...
Any help would be appreciated.
Thank you.
Mike Stevens
As I said, I have a database maintenance plan for these in which I am backing
up the transaction logs. They create a .trn file. The .trn file is
considerable smaller than the .ldf. The .ldf file just keeps growing.
Mike Stevens
"Tibor Karaszi" wrote:

> Are you 100% certain that you do perform log backup on this database? What does the virtual file
> structure look like? (See http://www.karaszi.com/SQLServer/info_dont_shrink.asp)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Stevens" <Stevens@.discussions.microsoft.com> wrote in message
> news:94506F40-C755-43D8-9256-96EEE68E4211@.microsoft.com...
>
|||On Feb 9, 8:25 am, Stevens <Stev...@.discussions.microsoft.com> wrote:
> I have several log files, .ldf, that keep growing and growing. I have a
> database maintenance plan for these in which I am backing up the transaction
> logs. How would I prevent these logs from continually getting larger? I
> have SQL 2000 running on a Windows 2003 standard server.
> I have other databases that the .ldf files don't grow...
> Any help would be appreciated.
> Thank you.
> --
> Mike Stevens
Is the database in question enabled for replication? Define "keeps
growing and growing". How fast is it growing? Does it grow every
day, or only on weekends? Are you running a reindex job or something
that might be producing large transactions?
|||I'm quite a rookie at SQL. I'm not sure how to tell if the database is
enabled for replication. The .ldf file grows about 20-30 meg. a day. I
don't think I'm running any reindexing job.
Thanks for your help.
Mike Stevens
"Tracy McKibben" wrote:

> On Feb 9, 8:25 am, Stevens <Stev...@.discussions.microsoft.com> wrote:
> Is the database in question enabled for replication? Define "keeps
> growing and growing". How fast is it growing? Does it grow every
> day, or only on weekends? Are you running a reindex job or something
> that might be producing large transactions?
>

Large Log Files

I have several log files, .ldf, that keep growing and growing. I have a
database maintenance plan for these in which I am backing up the transaction
logs. How would I prevent these logs from continually getting larger? I
have SQL 2000 running on a Windows 2003 standard server.
I have other databases that the .ldf files don't grow...
Any help would be appreciated.
Thank you.
--
Mike StevensAlso, do you have some old open transaction? (DBCC OPENTRAN)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Stevens" <Stevens@.discussions.microsoft.com> wrote in message
news:94506F40-C755-43D8-9256-96EEE68E4211@.microsoft.com...
>I have several log files, .ldf, that keep growing and growing. I have a
> database maintenance plan for these in which I am backing up the transacti
on
> logs. How would I prevent these logs from continually getting larger? I
> have SQL 2000 running on a Windows 2003 standard server.
> I have other databases that the .ldf files don't grow...
> Any help would be appreciated.
> Thank you.
> --
> Mike Stevens|||Are you 100% certain that you do perform log backup on this database? What d
oes the virtual file
structure look like? (See http://www.karaszi.com/SQLServer/in...ver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Stevens" <Stevens@.discussions.microsoft.com> wrote in message
news:94506F40-C755-43D8-9256-96EEE68E4211@.microsoft.com...
>I have several log files, .ldf, that keep growing and growing. I have a
> database maintenance plan for these in which I am backing up the transacti
on
> logs. How would I prevent these logs from continually getting larger? I
> have SQL 2000 running on a Windows 2003 standard server.
> I have other databases that the .ldf files don't grow...
> Any help would be appreciated.
> Thank you.
> --
> Mike Stevens|||As I said, I have a database maintenance plan for these in which I am backin
g
up the transaction logs. They create a .trn file. The .trn file is
considerable smaller than the .ldf. The .ldf file just keeps growing.
Mike Stevens
"Tibor Karaszi" wrote:

> Are you 100% certain that you do perform log backup on this database? What
does the virtual file
> structure look like? (See http://www.karaszi.com/SQLServer/in...ver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Stevens" <Stevens@.discussions.microsoft.com> wrote in message
> news:94506F40-C755-43D8-9256-96EEE68E4211@.microsoft.com...
>|||On Feb 9, 8:25 am, Stevens <Stev...@.discussions.microsoft.com> wrote:
> I have several log files, .ldf, that keep growing and growing. I have a
> database maintenance plan for these in which I am backing up the transacti
on
> logs. How would I prevent these logs from continually getting larger? I
> have SQL 2000 running on a Windows 2003 standard server.
> I have other databases that the .ldf files don't grow...
> Any help would be appreciated.
> Thank you.
> --
> Mike Stevens
Is the database in question enabled for replication? Define "keeps
growing and growing". How fast is it growing? Does it grow every
day, or only on weekends? Are you running a reindex job or something
that might be producing large transactions?|||What about the virtual log file structure, and result from DBCC OPENTRAN?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Stevens" <Stevens@.discussions.microsoft.com> wrote in message
news:CA33D6C4-850B-4E77-AADB-11EF06A11BBA@.microsoft.com...[vbcol=seagreen]
> As I said, I have a database maintenance plan for these in which I am back
ing
> up the transaction logs. They create a .trn file. The .trn file is
> considerable smaller than the .ldf. The .ldf file just keeps growing.
>
> --
> Mike Stevens
>
> "Tibor Karaszi" wrote:
>|||I'm quite a rookie at SQL. I'm not sure how to tell if the database is
enabled for replication. The .ldf file grows about 20-30 meg. a day. I
don't think I'm running any reindexing job.
Thanks for your help.
--
Mike Stevens
"Tracy McKibben" wrote:

> On Feb 9, 8:25 am, Stevens <Stev...@.discussions.microsoft.com> wrote:
> Is the database in question enabled for replication? Define "keeps
> growing and growing". How fast is it growing? Does it grow every
> day, or only on weekends? Are you running a reindex job or something
> that might be producing large transactions?
>

Large Log File

Have a log file that seems to be growing out of all proportion to the
database.
Have tried SHRINKDATABASE and SHRINKFILE
Get error message 'Cannot shrink log file because all logical files are in
use'
As all transactions completed ages ago, am puzzled as to why MSDE seems to
want to hang on to such a big log file.
How can I get MSDE to clear all the transactions, to the database, so can
reduce the log file size?
hi Kevin,
Kevin wrote:
> Have a log file that seems to be growing out of all proportion to the
> database.
> Have tried SHRINKDATABASE and SHRINKFILE
> Get error message 'Cannot shrink log file because all logical files
> are in use'
> As all transactions completed ages ago, am puzzled as to why MSDE
> seems to want to hang on to such a big log file.
> How can I get MSDE to clear all the transactions, to the database, so
> can reduce the log file size?
perform a BACKUP LOG
(http://msdn.microsoft.com/library/de...ba-bz_35ww.asp
) in order to consolidates aged transactions and clear all virtual logs...
then shrink the log file..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Works brilliantly well - Thanks Andrea
Kevin
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3og1vbF55dn8U1@.individual.net...
> hi Kevin,
> Kevin wrote:
> perform a BACKUP LOG
> (http://msdn.microsoft.com/library/de...ba-bz_35ww.asp
> ) in order to consolidates aged transactions and clear all virtual
> logs... then shrink the log file..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>

Large Inserts, TempDB Growing

I have a query that's joining a messload of tables to populate a single
table used later for OLAP reporting.
The source tables, and the OLAP table are in different databases.
Basic Form:
INSERT INTO OLAPDB.dbo.SomeTable
SELECT lots_of_columns
FROM atables
INNER JOIN lots_of_tables...
No ORDER BYs... no GROUP BYs
The query dies because there isn't enough disk space for TempDB.
When I look at the files, tempdb is HUGE, and the OLAPDB (destination)
is tiny.
Is there a way to insert the data straight into the destination?
without it using tempdb as an intermediate?
any ideas?
thanks
-MarkMark,
Maybe add a WHERE clause and perform the INSERT in multiple parts.
HTH
Jerry
"Mark" <AnonymousPerson12345@.gmail.com> wrote in message
news:1127429078.936480.53900@.o13g2000cwo.googlegroups.com...
>I have a query that's joining a messload of tables to populate a single
> table used later for OLAP reporting.
> The source tables, and the OLAP table are in different databases.
> Basic Form:
> INSERT INTO OLAPDB.dbo.SomeTable
> SELECT lots_of_columns
> FROM atables
> INNER JOIN lots_of_tables...
> No ORDER BYs... no GROUP BYs
> The query dies because there isn't enough disk space for TempDB.
> When I look at the files, tempdb is HUGE, and the OLAPDB (destination)
> is tiny.
> Is there a way to insert the data straight into the destination?
> without it using tempdb as an intermediate?
> any ideas?
> thanks
> -Mark
>|||Mark wrote:
> I have a query that's joining a messload of tables to populate a
> single table used later for OLAP reporting.
> The source tables, and the OLAP table are in different databases.
> Basic Form:
> INSERT INTO OLAPDB.dbo.SomeTable
> SELECT lots_of_columns
> FROM atables
> INNER JOIN lots_of_tables...
> No ORDER BYs... no GROUP BYs
> The query dies because there isn't enough disk space for TempDB.
> When I look at the files, tempdb is HUGE, and the OLAPDB (destination)
> is tiny.
> Is there a way to insert the data straight into the destination?
> without it using tempdb as an intermediate?
> any ideas?
> thanks
> -Mark
An INSERT INTO is a fully logged operation. A SELECT INTO OTOH is a bulk
logged operation. Instead of using tempdb, you could use a regular table
in a database of your choosing. However, if you are running out of
space in tempdb, you could make sure tempdb is adequately sized to begin
with and can auto-grow if needed. You could also try using SELECT INTO
which will keep transaction logging to a minimum, but does require SQL
Server create the table for you based on the columns in the query. You
could also try speeding up the query by using a stored procedure that
pulls data from the tables in a more efficient manner - if that's
possible.
David Gugick
Quest Software
www.imceda.com
www.quest.com