@ local variable makes query slower?

Hi all,

I have a query that scans huge table consists of 8 or more millions
records. The funny thing is that if I use the query with local
variable, the query takes more than 1 minutes, whereas if I hard code
the value into the query, it takes about 1 second. Here are the
queries:

WITH VARIABLE:
------

DECLARE @.i_StartDate DATETIME
DECLARE @.i_EndDate DATETIME
SET @.i_StartDate = '2004-04-26'
SET @.i_EndDate = '2004-04-28'

SELECT DISTINCT A.EventId, A.[Date], A.UserId, C.[Name] AS
EventTypeName, D.[Name] AS EventSubTypeName, A.[Text], A.Data
FROM TableEvent A, TableCSRep B, TableEventType C, TableEventSubType D
WHERE (A.[Date] >= @.i_StartDate AND A.[Date] <= @.i_EndDate)

...And some other conditions

-------

WITHOUT VARIABLE:

SELECT DISTINCT A.EventId, A.[Date], A.UserId, C.[Name] AS
EventTypeName, D.[Name] AS EventSubTypeName, A.[Text], A.Data
FROM TableEvent A, TableCSRep B, TableEventType C, TableEventSubType D
WHERE (A.[Date] >= '2004-04-26' AND A.[Date] <= '2004-04-28')

...And some other conditions

-------

The later one runs significantly faster than the first one. I've
isolated the problem at the local variable @.i_StartDate and
@.i_EndDate. Can somebody help me out, Please...

Thank you,
Michelle."Michelle" <michelletran@.harmonyremote.com> wrote in message
news:56c5b7ab.0404260656.281cfc40@.posting.google.c om...
> Hi all,
> I have a query that scans huge table consists of 8 or more millions
> records. The funny thing is that if I use the query with local
> variable, the query takes more than 1 minutes, whereas if I hard code
> the value into the query, it takes about 1 second. Here are the
> queries:
> WITH VARIABLE:
> ------
> DECLARE @.i_StartDate DATETIME
> DECLARE @.i_EndDate DATETIME
> SET @.i_StartDate = '2004-04-26'
> SET @.i_EndDate = '2004-04-28'
>
> SELECT DISTINCT A.EventId, A.[Date], A.UserId, C.[Name] AS
> EventTypeName, D.[Name] AS EventSubTypeName, A.[Text], A.Data
> FROM TableEvent A, TableCSRep B, TableEventType C, TableEventSubType D
> WHERE (A.[Date] >= @.i_StartDate AND A.[Date] <= @.i_EndDate)
> ...And some other conditions
> -------
> WITHOUT VARIABLE:
>
> SELECT DISTINCT A.EventId, A.[Date], A.UserId, C.[Name] AS
> EventTypeName, D.[Name] AS EventSubTypeName, A.[Text], A.Data
> FROM TableEvent A, TableCSRep B, TableEventType C, TableEventSubType D
> WHERE (A.[Date] >= '2004-04-26' AND A.[Date] <= '2004-04-28')
> ...And some other conditions
> -------
> The later one runs significantly faster than the first one. I've
> isolated the problem at the local variable @.i_StartDate and
> @.i_EndDate. Can somebody help me out, Please...
> Thank you,
> Michelle.

This may be an example of parameter sniffing - see this post, for example,
which describes an almost identical case:

http://groups.google.com/groups?hl=...ftngp13.phx.gbl

Simon|||Thanks Simon.
Michelle.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

.wrk file in Logshipping 2005

This is about SQLServer 2005 Logshipping.

I am looking at Logshiping folder and seeing huge file like DBName_20070313210008.wrk

Any Idea? what the file is about

Thanks

--

Farhan

Do you see many files or only 1file?|||Only one.|||

The .wrk files are produced when the transaction log backups are copied from the backup location to the secondary server (by the agent job on the secondary). The files are named .wrk during the copy operation; when they have been completely copied they are renamed to have the .trn extension. The temporary naming using the .wrk extension ensures that the files are not picked up by the restore job until successfully copied.

So, if the .wrk file you are seeing is there all the time it is likely to be an old copy that failed for some reason. So for normal operation, you'll see a .wrk file when each transaction log backup file is copied and then that will be renamed to a .trn file.

.wrk file in Logshipping 2005

This is about SQLServer 2005 Logshipping.

I am looking at Logshiping folder and seeing huge file like DBName_20070313210008.wrk

Any Idea? what the file is about

Thanks

--

Farhan

Do you see many files or only 1file?|||Only one.|||

The .wrk files are produced when the transaction log backups are copied from the backup location to the secondary server (by the agent job on the secondary). The files are named .wrk during the copy operation; when they have been completely copied they are renamed to have the .trn extension. The temporary naming using the .wrk extension ensures that the files are not picked up by the restore job until successfully copied.

So, if the .wrk file you are seeing is there all the time it is likely to be an old copy that failed for some reason. So for normal operation, you'll see a .wrk file when each transaction log backup file is copied and then that will be renamed to a .trn file.

.wrk file in Logshipping 2005

This is about SQLServer 2005 Logshipping.

I am looking at Logshiping folder and seeing huge file like DBName_20070313210008.wrk

Any Idea? what the file is about

Thanks

--

Farhan

Do you see many files or only 1file?|||Only one.|||

The .wrk files are produced when the transaction log backups are copied from the backup location to the secondary server (by the agent job on the secondary). The files are named .wrk during the copy operation; when they have been completely copied they are renamed to have the .trn extension. The temporary naming using the .wrk extension ensures that the files are not picked up by the restore job until successfully copied.

So, if the .wrk file you are seeing is there all the time it is likely to be an old copy that failed for some reason. So for normal operation, you'll see a .wrk file when each transaction log backup file is copied and then that will be renamed to a .trn file.

.bak file format?

Hi.

I was wondering if anybody could piont me to a reference for the .bak
file format.

I need to upload a huge amount of data from a unix machine into an MS
SQLServer database and have tried lots of different things with mixed
results.

I occurs to me that if I can generate a .bak file on the unix side,
move it over to the MS side and 'restore' the database, that would
probably be the fastest possible method.

Thanks,

-jimAssuming that you are referring to the MS-specific binary .bak DB backup
file format, I would be very surprised if MS releases those types of
details. It could/would prevent them from changing the format..

Even aside from this, generating a .bak file on the Unix side is
unlikely. There are potential big-endian, little-endian issues. You do
know that SQL Server runs only on Windows, yes?

Is there another DB on the unix side? Could you hook up to it as a
linked server w/ an OLE DB driver or something?

I would export in ascii delimited CSV format or something, and then
import into SQL server w/ the integration services import/export wizard.

hth

Allen Jantzen

jims wrote:

Quote:

Originally Posted by

Hi.
>
I was wondering if anybody could piont me to a reference for the .bak
file format.
>
I need to upload a huge amount of data from a unix machine into an MS
SQLServer database and have tried lots of different things with mixed
results.
>
I occurs to me that if I can generate a .bak file on the unix side,
move it over to the MS side and 'restore' the database, that would
probably be the fastest possible method.
>
Thanks,
>
-jim
>

|||jims (jim@.asrc.cestm.albany.edu) writes:

Quote:

Originally Posted by

I was wondering if anybody could piont me to a reference for the .bak
file format.
>
I need to upload a huge amount of data from a unix machine into an MS
SQLServer database and have tried lots of different things with mixed
results.
>
I occurs to me that if I can generate a .bak file on the unix side,
move it over to the MS side and 'restore' the database, that would
probably be the fastest possible method.

It would take you ages until you have produced that .bak file. Your data
will be stale by then.

The fastest way to load lots of data into SQL Server is bulk copy. You have
a lot more options if you first put the file in a place where you can access
it from Windows. The simplest is probably to use the command-line tool
BCP. You need to format the data in a way so that BCP can read it. (But
BCP is flexible.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

..\MSSQL\Data\ - Directory saturated

C:\Program Files\Microsoft SQL Server\MSSQL\Data\" contains .mdb and
..ldb files that are huge, some reach 21GB! while backup files don't
exceed 8MB.

I guess it's a kind of transaction log, but I'm not sure.

Can I erase them all and lose no information?
Where I configure it to avoid it to happen in the future?

Thanks in advance.Try to shrink the database. Run the SQL Server Enterprise manager.
Right-click on the database of interest and select "all tasks | shrink
database" from the context menu. Try this witthout and then with the "move
pages" option checked. Free space "holes" can develop in your database as
storage expands and rows are added/deleted/updated. Doing index maintenance
may help, too but at 8MB vs 21GB, I'm thinking it's not just sparse page
fills but wholesale extents. You could set the databases for autoshrink but
this might cause operations in your database to slow down when you weren't
expecting it. Probably better to do it on a schedule.

You might also look at the properties and see how much space is used versus
allocated. Before looking at properties, be sure to right-click on the
database and do a "refresh" to get the figures updated.

Could be your log has never been dumped and truncated. You may have to set
up a maintenance plan. Check the Administrator's guide for advice. The log
is in the .LDF file.

"Pablo" <pablopettis@.yahoo.com> wrote in message
news:47ae7cc6.0407150630.4ad7be1d@.posting.google.c om...
> C:\Program Files\Microsoft SQL Server\MSSQL\Data\" contains .mdb and
> .ldb files that are huge, some reach 21GB! while backup files don't
> exceed 8MB.
> I guess it's a kind of transaction log, but I'm not sure.
> Can I erase them all and lose no information?
> Where I configure it to avoid it to happen in the future?
> Thanks in advance.|||Hi

Check out:
http://msdn.microsoft.com/library/d...ar_da2_1uzr.asp

If this is production machine you should think about moving the log files
onto a different set of spindles (disks/controller card) to the data files,
operating system and page file.

John

"Pablo" <pablopettis@.yahoo.com> wrote in message
news:47ae7cc6.0407150630.4ad7be1d@.posting.google.c om...
> C:\Program Files\Microsoft SQL Server\MSSQL\Data\" contains .mdb and
> .ldb files that are huge, some reach 21GB! while backup files don't
> exceed 8MB.
> I guess it's a kind of transaction log, but I'm not sure.
> Can I erase them all and lose no information?
> Where I configure it to avoid it to happen in the future?
> Thanks in advance.