Query Speed

microsoft.public.sqlserver.programming

19 Aug 2005 8:41 PM

jmeyers

I've got a query that does a select of specific fields from a table. I'm
finding that when I trim down the record length to approx 793 bytes, the
query returns approx. 14,000 records in 1 second. When I go over the 793
bytes, the query (returning the same 14,000 records) takes about 30 seconds.

Is there anything I can do to gain speed when the record length is more than
793 bytes? Here's the query:

SELECT iTrackingId AS [TrackingId]
,iActionId AS [ActionId]
,dtActionDate AS [ActionDate]
,iIndividualId AS [IndId]
,iReferenceID AS [RefId]
,iEncryptedIndividualId AS [EncryptId]
,vchFirstName AS [FirstName]
,vchLastName AS [LastName]
,vchTitleDesc AS [Title]
,vchAddress1 AS [Address1]
,vchAddress2 AS [Address2]
,vchAddress3 AS [Address3]
,vchCity AS [City]
,chIndRegionName AS [State]
,vchPostCode AS [Zip]
,chIndCountryDesc AS [Country]
,vchEmailAddress AS [Email]
,vchPhoneNumber AS [Phone]
,iCompanyId AS [CompanyId]
,vchCompanyName AS [Company]
,chCompRegionName AS [CmpState]
,chCompCountryDesc AS [CmpCountry]
,vchCompanyType AS [CmpType]
,vchCompanySubType AS [CmpSubType]
,vchCompanyStatus AS [CmpStatus]
,vchMarketSector AS [SlsChnl]
,vchSIC AS [SIC]
,vchSICDescription AS [SICDesc]
,vchSpecificSIC AS [SpecSIC]
,chFieldRep AS [SalesRep]
,chAccountMgr AS [ISR]
,chDTMRep AS [DTMRep]
,chLeadCenterRep AS [LeadCntRep]
FROM fl_listgenexport
WHERE iExportid = @iExportid

19 Aug 2005 9:31 PM

What do you mean "trimming the record length" ??? What are doing?

You're problem likely does not have to do with the size of each record. If
you can provide more info we may be able to offer some suggestions.

Show quote

"jmeyers" wrote:

> I've got a query that does a select of specific fields from a table. I'm
> finding that when I trim down the record length to approx 793 bytes, the
> query returns approx. 14,000 records in 1 second. When I go over the 793
> bytes, the query (returning the same 14,000 records) takes about 30 seconds.
>
> Is there anything I can do to gain speed when the record length is more than
> 793 bytes? Here's the query:
>
> SELECT iTrackingId AS [TrackingId]
> ,iActionId AS [ActionId]
> ,dtActionDate AS [ActionDate]
> ,iIndividualId AS [IndId]
> ,iReferenceID AS [RefId]
> ,iEncryptedIndividualId AS [EncryptId]
> ,vchFirstName AS [FirstName]
> ,vchLastName AS [LastName]
> ,vchTitleDesc AS [Title]
> ,vchAddress1 AS [Address1]
> ,vchAddress2 AS [Address2]
> ,vchAddress3 AS [Address3]
> ,vchCity AS [City]
> ,chIndRegionName AS [State]
> ,vchPostCode AS [Zip]
> ,chIndCountryDesc AS [Country]
> ,vchEmailAddress AS [Email]
> ,vchPhoneNumber AS [Phone]
> ,iCompanyId AS [CompanyId]
> ,vchCompanyName AS [Company]
> ,chCompRegionName AS [CmpState]
> ,chCompCountryDesc AS [CmpCountry]
> ,vchCompanyType AS [CmpType]
> ,vchCompanySubType AS [CmpSubType]
> ,vchCompanyStatus AS [CmpStatus]
> ,vchMarketSector AS [SlsChnl]
> ,vchSIC AS [SIC]
> ,vchSICDescription AS [SICDesc]
> ,vchSpecificSIC AS [SpecSIC]
> ,chFieldRep AS [SalesRep]
> ,chAccountMgr AS [ISR]
> ,chDTMRep AS [DTMRep]
> ,chLeadCenterRep AS [LeadCntRep]
> FROM fl_listgenexport
> WHERE iExportid = @iExportid
>
>

20 Aug 2005 3:09 AM

Louis Davidson

Check / post the plan for each query. There might be a big difference. If
there is not a big difference between the queries at a plan level, try doing
something like:

select count(*)
from ( your query) as test

This is a trick I use occasionally with big queries to see how quickly
things will run without having to return actual data.

Then would look at the Client Statistics to see what might be happening. It
might be that you are hitting some threshold of your networking.

Either way,check this stuff and post back. This is pretty interesting and
hopefully something you can find evidence of what is different in these
places/

--
----------------------------------------------------------------------------
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP

Show quote

"jmeyers" <jmey***@discussions.microsoft.com> wrote in message
news:B9F52B4F-58B4-4084-ADC2-D29909237A47@microsoft.com...
> I've got a query that does a select of specific fields from a table. I'm
> finding that when I trim down the record length to approx 793 bytes, the
> query returns approx. 14,000 records in 1 second. When I go over the 793
> bytes, the query (returning the same 14,000 records) takes about 30
> seconds.
>
> Is there anything I can do to gain speed when the record length is more
> than
> 793 bytes? Here's the query:
>
> SELECT iTrackingId AS [TrackingId]
> ,iActionId AS [ActionId]
> ,dtActionDate AS [ActionDate]
> ,iIndividualId AS [IndId]
> ,iReferenceID AS [RefId]
> ,iEncryptedIndividualId AS [EncryptId]
> ,vchFirstName AS [FirstName]
> ,vchLastName AS [LastName]
> ,vchTitleDesc AS [Title]
> ,vchAddress1 AS [Address1]
> ,vchAddress2 AS [Address2]
> ,vchAddress3 AS [Address3]
> ,vchCity AS [City]
> ,chIndRegionName AS [State]
> ,vchPostCode AS [Zip]
> ,chIndCountryDesc AS [Country]
> ,vchEmailAddress AS [Email]
> ,vchPhoneNumber AS [Phone]
> ,iCompanyId AS [CompanyId]
> ,vchCompanyName AS [Company]
> ,chCompRegionName AS [CmpState]
> ,chCompCountryDesc AS [CmpCountry]
> ,vchCompanyType AS [CmpType]
> ,vchCompanySubType AS [CmpSubType]
> ,vchCompanyStatus AS [CmpStatus]
> ,vchMarketSector AS [SlsChnl]
> ,vchSIC AS [SIC]
> ,vchSICDescription AS [SICDesc]
> ,vchSpecificSIC AS [SpecSIC]
> ,chFieldRep AS [SalesRep]
> ,chAccountMgr AS [ISR]
> ,chDTMRep AS [DTMRep]
> ,chLeadCenterRep AS [LeadCntRep]
> FROM fl_listgenexport
> WHERE iExportid = @iExportid
>
>

21 Aug 2005 12:05 PM

Hugo Kornelis

On Fri, 19 Aug 2005 22:09:00 -0500, Louis Davidson wrote:

>Check / post the plan for each query. There might be a big difference. If
>there is not a big difference between the queries at a plan level, try doing
>something like:
>
>select count(*)
>from ( your query) as test
>
>This is a trick I use occasionally with big queries to see how quickly
>things will run without having to return actual data.

Hi Louis,

Nice trick, but it won't always produce dependable results. In some
cases, the COUNT(*) query might use an index that can't be used for the
full query, so there will either be an extra bookmark lookup or a
completely different plan for the "real" query.

A more dependable way to test performance of a query without returning
data is to dump the results in a temp table:

SELECT ???, ???, ??? -- As in "real" query
INTO #TempTable
FROM -- Rest of "real" query
WHERE -- ditto
go
DROP TABLE #TempTable
go

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)

21 Aug 2005 1:01 PM

Stu

Hugo,

I haven't tested this yet (but I will tomorrow), but do you think that
using a table variable would accomplish this same objective.
Obviously, you'd have to declare the variable ahead of time, and then
INSERT into it, but at least you'd get to look at a real execution plan
during the INSERT and DROP process.

Just curious,
Stu

21 Aug 2005 8:19 PM

Hugo Kornelis

On 21 Aug 2005 06:01:27 -0700, Stu wrote:

>Hugo,
>
>I haven't tested this yet (but I will tomorrow), but do you think that
>using a table variable would accomplish this same objective.
>Obviously, you'd have to declare the variable ahead of time, and then
>INSERT into it, but at least you'd get to look at a real execution plan
>during the INSERT and DROP process.

Hi Stu,

Yeah, I think that would do as well. But wouldn't you see the same
execution plan if you use SELECT ... INTO #Temp?

In production code, I always create my temp tables and declare my table
variables at the start of the procedure that uses them - it gives extra
clarity, it helps to reduce recompiles, and it gives me more opportunity
to add extra indexes. With SELECT ... INTO, I lack the level of control
that I want.

But for this purely performance-testing related purpose, I see no reason
to take those extra steps. Just adding the INTO #Temp line between
SELECT and FROM and the DROP TABLE at the end is all I have to do.

Yes - I am lazy. <g>

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)

21 Aug 2005 10:20 PM

Stu

I guess so; I just like table variables because I can use them in
estimated execution plans as well as real plans. It's just easier at
times to debug things while building scripts to check the estimated
plans; I was just curious if you could think of any reason to
explicitly use temporary tables instead of table variables.

Stu

21 Aug 2005 10:39 PM

Hugo Kornelis

On 21 Aug 2005 15:20:11 -0700, Stu wrote:

(snip)

> I was just curious if you could think of any reason to
>explicitly use temporary tables instead of table variables.

Hi Stu,

In this case, none other than laziness. <smile>

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)

21 Aug 2005 8:05 PM

Louis Davidson

Thanks, Hugo,

Yeah, I might do that next too. You are right, but usually count(*) is a
good indicator and it doesn't require so much disk space (I probably should
have noted that.) Another way is to have the UI discard results.

--
----------------------------------------------------------------------------
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)

Show quote

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:targg1tgf99tuas3it82ss1ph50ivskbq8@4ax.com...
> On Fri, 19 Aug 2005 22:09:00 -0500, Louis Davidson wrote:
>
>>Check / post the plan for each query. There might be a big difference.
>>If
>>there is not a big difference between the queries at a plan level, try
>>doing
>>something like:
>>
>>select count(*)
>>from ( your query) as test
>>
>>This is a trick I use occasionally with big queries to see how quickly
>>things will run without having to return actual data.
>
> Hi Louis,
>
> Nice trick, but it won't always produce dependable results. In some
> cases, the COUNT(*) query might use an index that can't be used for the
> full query, so there will either be an extra bookmark lookup or a
> completely different plan for the "real" query.
>
> A more dependable way to test performance of a query without returning
> data is to dump the results in a temp table:
>
> SELECT ???, ???, ??? -- As in "real" query
> INTO #TempTable
> FROM -- Rest of "real" query
> WHERE -- ditto
> go
> DROP TABLE #TempTable
> go
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)

22 Aug 2005 12:36 PM

jmeyers

I did as you suggested:

select count(*) from ( your query) as test

It cam back as 6 ms. In the query posted below, when I say 'trim down' the
select, specifically, if I run this query:

SELECT iTrackingId AS [TrackingId]
,iActionId AS [ActionId]
,dtActionDate AS [ActionDate]
,iIndividualId AS [IndId]
,iReferenceID AS [RefId]
,iEncryptedIndividualId AS [EncryptId]
,vchFirstName AS [FirstName]
,vchLastName AS [LastName]
,vchTitleDesc AS [Title]
FROM fl_listgenexport
WHERE iExportid = @iExportid

It also returns back in 6 ms. If I add even one more field, it immediately
jumps back up to 30 seconds.

Not sure where or what to look at with the client stats.

Show quote

"Louis Davidson" wrote:

> Check / post the plan for each query. There might be a big difference. If
> there is not a big difference between the queries at a plan level, try doing
> something like:
>
> select count(*)
> from ( your query) as test
>
> This is a trick I use occasionally with big queries to see how quickly
> things will run without having to return actual data.
>
> Then would look at the Client Statistics to see what might be happening. It
> might be that you are hitting some threshold of your networking.
>
> Either way,check this stuff and post back. This is pretty interesting and
> hopefully something you can find evidence of what is different in these
> places/
>
> --
> ----------------------------------------------------------------------------
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
>
> "jmeyers" <jmey***@discussions.microsoft.com> wrote in message
> news:B9F52B4F-58B4-4084-ADC2-D29909237A47@microsoft.com...
> > I've got a query that does a select of specific fields from a table. I'm
> > finding that when I trim down the record length to approx 793 bytes, the
> > query returns approx. 14,000 records in 1 second. When I go over the 793
> > bytes, the query (returning the same 14,000 records) takes about 30
> > seconds.
> >
> > Is there anything I can do to gain speed when the record length is more
> > than
> > 793 bytes? Here's the query:
> >
> > SELECT iTrackingId AS [TrackingId]
> > ,iActionId AS [ActionId]
> > ,dtActionDate AS [ActionDate]
> > ,iIndividualId AS [IndId]
> > ,iReferenceID AS [RefId]
> > ,iEncryptedIndividualId AS [EncryptId]
> > ,vchFirstName AS [FirstName]
> > ,vchLastName AS [LastName]
> > ,vchTitleDesc AS [Title]
> > ,vchAddress1 AS [Address1]
> > ,vchAddress2 AS [Address2]
> > ,vchAddress3 AS [Address3]
> > ,vchCity AS [City]
> > ,chIndRegionName AS [State]
> > ,vchPostCode AS [Zip]
> > ,chIndCountryDesc AS [Country]
> > ,vchEmailAddress AS [Email]
> > ,vchPhoneNumber AS [Phone]
> > ,iCompanyId AS [CompanyId]
> > ,vchCompanyName AS [Company]
> > ,chCompRegionName AS [CmpState]
> > ,chCompCountryDesc AS [CmpCountry]
> > ,vchCompanyType AS [CmpType]
> > ,vchCompanySubType AS [CmpSubType]
> > ,vchCompanyStatus AS [CmpStatus]
> > ,vchMarketSector AS [SlsChnl]
> > ,vchSIC AS [SIC]
> > ,vchSICDescription AS [SICDesc]
> > ,vchSpecificSIC AS [SpecSIC]
> > ,chFieldRep AS [SalesRep]
> > ,chAccountMgr AS [ISR]
> > ,chDTMRep AS [DTMRep]
> > ,chLeadCenterRep AS [LeadCntRep]
> > FROM fl_listgenexport
> > WHERE iExportid = @iExportid
> >
> >
>
>
>

22 Aug 2005 1:03 PM

Tibor Karaszi

Possibly, as you remove columns from the SELECT, you end up with a covered query (all columns
participates in a non-clustered index). If such a case, all data is found in the index and non data
page access is needed.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quote

"jmeyers" <jmey***@discussions.microsoft.com> wrote in message
news:D482206E-E636-47A6-9A91-4EFE43CA2310@microsoft.com...
>I did as you suggested:
>
> select count(*) from ( your query) as test
>
> It cam back as 6 ms. In the query posted below, when I say 'trim down' the
> select, specifically, if I run this query:
>
> SELECT iTrackingId AS [TrackingId]
> ,iActionId AS [ActionId]
> ,dtActionDate AS [ActionDate]
> ,iIndividualId AS [IndId]
> ,iReferenceID AS [RefId]
> ,iEncryptedIndividualId AS [EncryptId]
> ,vchFirstName AS [FirstName]
> ,vchLastName AS [LastName]
> ,vchTitleDesc AS [Title]
> FROM fl_listgenexport
> WHERE iExportid = @iExportid
>
> It also returns back in 6 ms. If I add even one more field, it immediately
> jumps back up to 30 seconds.
>
> Not sure where or what to look at with the client stats.
>
>
>
> "Louis Davidson" wrote:
>
>> Check / post the plan for each query. There might be a big difference. If
>> there is not a big difference between the queries at a plan level, try doing
>> something like:
>>
>> select count(*)
>> from ( your query) as test
>>
>> This is a trick I use occasionally with big queries to see how quickly
>> things will run without having to return actual data.
>>
>> Then would look at the Client Statistics to see what might be happening. It
>> might be that you are hitting some threshold of your networking.
>>
>> Either way,check this stuff and post back. This is pretty interesting and
>> hopefully something you can find evidence of what is different in these
>> places/
>>
>> --
>> ----------------------------------------------------------------------------
>> Louis Davidson - http://spaces.msn.com/members/drsql/
>> SQL Server MVP
>>
>>
>> "jmeyers" <jmey***@discussions.microsoft.com> wrote in message
>> news:B9F52B4F-58B4-4084-ADC2-D29909237A47@microsoft.com...
>> > I've got a query that does a select of specific fields from a table. I'm
>> > finding that when I trim down the record length to approx 793 bytes, the
>> > query returns approx. 14,000 records in 1 second. When I go over the 793
>> > bytes, the query (returning the same 14,000 records) takes about 30
>> > seconds.
>> >
>> > Is there anything I can do to gain speed when the record length is more
>> > than
>> > 793 bytes? Here's the query:
>> >
>> > SELECT iTrackingId AS [TrackingId]
>> > ,iActionId AS [ActionId]
>> > ,dtActionDate AS [ActionDate]
>> > ,iIndividualId AS [IndId]
>> > ,iReferenceID AS [RefId]
>> > ,iEncryptedIndividualId AS [EncryptId]
>> > ,vchFirstName AS [FirstName]
>> > ,vchLastName AS [LastName]
>> > ,vchTitleDesc AS [Title]
>> > ,vchAddress1 AS [Address1]
>> > ,vchAddress2 AS [Address2]
>> > ,vchAddress3 AS [Address3]
>> > ,vchCity AS [City]
>> > ,chIndRegionName AS [State]
>> > ,vchPostCode AS [Zip]
>> > ,chIndCountryDesc AS [Country]
>> > ,vchEmailAddress AS [Email]
>> > ,vchPhoneNumber AS [Phone]
>> > ,iCompanyId AS [CompanyId]
>> > ,vchCompanyName AS [Company]
>> > ,chCompRegionName AS [CmpState]
>> > ,chCompCountryDesc AS [CmpCountry]
>> > ,vchCompanyType AS [CmpType]
>> > ,vchCompanySubType AS [CmpSubType]
>> > ,vchCompanyStatus AS [CmpStatus]
>> > ,vchMarketSector AS [SlsChnl]
>> > ,vchSIC AS [SIC]
>> > ,vchSICDescription AS [SICDesc]
>> > ,vchSpecificSIC AS [SpecSIC]
>> > ,chFieldRep AS [SalesRep]
>> > ,chAccountMgr AS [ISR]
>> > ,chDTMRep AS [DTMRep]
>> > ,chLeadCenterRep AS [LeadCntRep]
>> > FROM fl_listgenexport
>> > WHERE iExportid = @iExportid
>> >
>> >
>>
>>
>>

22 Aug 2005 1:29 PM

jmeyers

The only index on the table is clustered on the iExportId.

Show quote

"Tibor Karaszi" wrote:

> Possibly, as you remove columns from the SELECT, you end up with a covered query (all columns
> participates in a non-clustered index). If such a case, all data is found in the index and non data
> page access is needed.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
>
> "jmeyers" <jmey***@discussions.microsoft.com> wrote in message
> news:D482206E-E636-47A6-9A91-4EFE43CA2310@microsoft.com...
> >I did as you suggested:
> >
> > select count(*) from ( your query) as test
> >
> > It cam back as 6 ms. In the query posted below, when I say 'trim down' the
> > select, specifically, if I run this query:
> >
> > SELECT iTrackingId AS [TrackingId]
> > ,iActionId AS [ActionId]
> > ,dtActionDate AS [ActionDate]
> > ,iIndividualId AS [IndId]
> > ,iReferenceID AS [RefId]
> > ,iEncryptedIndividualId AS [EncryptId]
> > ,vchFirstName AS [FirstName]
> > ,vchLastName AS [LastName]
> > ,vchTitleDesc AS [Title]
> > FROM fl_listgenexport
> > WHERE iExportid = @iExportid
> >
> > It also returns back in 6 ms. If I add even one more field, it immediately
> > jumps back up to 30 seconds.
> >
> > Not sure where or what to look at with the client stats.
> >
> >
> >
> > "Louis Davidson" wrote:
> >
> >> Check / post the plan for each query. There might be a big difference. If
> >> there is not a big difference between the queries at a plan level, try doing
> >> something like:
> >>
> >> select count(*)
> >> from ( your query) as test
> >>
> >> This is a trick I use occasionally with big queries to see how quickly
> >> things will run without having to return actual data.
> >>
> >> Then would look at the Client Statistics to see what might be happening. It
> >> might be that you are hitting some threshold of your networking.
> >>
> >> Either way,check this stuff and post back. This is pretty interesting and
> >> hopefully something you can find evidence of what is different in these
> >> places/
> >>
> >> --
> >> ----------------------------------------------------------------------------
> >> Louis Davidson - http://spaces.msn.com/members/drsql/
> >> SQL Server MVP
> >>
> >>
> >> "jmeyers" <jmey***@discussions.microsoft.com> wrote in message
> >> news:B9F52B4F-58B4-4084-ADC2-D29909237A47@microsoft.com...
> >> > I've got a query that does a select of specific fields from a table. I'm
> >> > finding that when I trim down the record length to approx 793 bytes, the
> >> > query returns approx. 14,000 records in 1 second. When I go over the 793
> >> > bytes, the query (returning the same 14,000 records) takes about 30
> >> > seconds.
> >> >
> >> > Is there anything I can do to gain speed when the record length is more
> >> > than
> >> > 793 bytes? Here's the query:
> >> >
> >> > SELECT iTrackingId AS [TrackingId]
> >> > ,iActionId AS [ActionId]
> >> > ,dtActionDate AS [ActionDate]
> >> > ,iIndividualId AS [IndId]
> >> > ,iReferenceID AS [RefId]
> >> > ,iEncryptedIndividualId AS [EncryptId]
> >> > ,vchFirstName AS [FirstName]
> >> > ,vchLastName AS [LastName]
> >> > ,vchTitleDesc AS [Title]
> >> > ,vchAddress1 AS [Address1]
> >> > ,vchAddress2 AS [Address2]
> >> > ,vchAddress3 AS [Address3]
> >> > ,vchCity AS [City]
> >> > ,chIndRegionName AS [State]
> >> > ,vchPostCode AS [Zip]
> >> > ,chIndCountryDesc AS [Country]
> >> > ,vchEmailAddress AS [Email]
> >> > ,vchPhoneNumber AS [Phone]
> >> > ,iCompanyId AS [CompanyId]
> >> > ,vchCompanyName AS [Company]
> >> > ,chCompRegionName AS [CmpState]
> >> > ,chCompCountryDesc AS [CmpCountry]
> >> > ,vchCompanyType AS [CmpType]
> >> > ,vchCompanySubType AS [CmpSubType]
> >> > ,vchCompanyStatus AS [CmpStatus]
> >> > ,vchMarketSector AS [SlsChnl]
> >> > ,vchSIC AS [SIC]
> >> > ,vchSICDescription AS [SICDesc]
> >> > ,vchSpecificSIC AS [SpecSIC]
> >> > ,chFieldRep AS [SalesRep]
> >> > ,chAccountMgr AS [ISR]
> >> > ,chDTMRep AS [DTMRep]
> >> > ,chLeadCenterRep AS [LeadCntRep]
> >> > FROM fl_listgenexport
> >> > WHERE iExportid = @iExportid
> >> >
> >> >
> >>
> >>
> >>
>
>

22 Aug 2005 1:35 PM

Tibor Karaszi

Did you check the execution plan?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quote

"jmeyers" <jmey***@discussions.microsoft.com> wrote in message
news:50AAF174-CABD-4383-A018-992A9322BDF0@microsoft.com...
> The only index on the table is clustered on the iExportId.
>
> "Tibor Karaszi" wrote:
>
>> Possibly, as you remove columns from the SELECT, you end up with a covered query (all columns
>> participates in a non-clustered index). If such a case, all data is found in the index and non
>> data
>> page access is needed.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>>
>> "jmeyers" <jmey***@discussions.microsoft.com> wrote in message
>> news:D482206E-E636-47A6-9A91-4EFE43CA2310@microsoft.com...
>> >I did as you suggested:
>> >
>> > select count(*) from ( your query) as test
>> >
>> > It cam back as 6 ms. In the query posted below, when I say 'trim down' the
>> > select, specifically, if I run this query:
>> >
>> > SELECT iTrackingId AS [TrackingId]
>> > ,iActionId AS [ActionId]
>> > ,dtActionDate AS [ActionDate]
>> > ,iIndividualId AS [IndId]
>> > ,iReferenceID AS [RefId]
>> > ,iEncryptedIndividualId AS [EncryptId]
>> > ,vchFirstName AS [FirstName]
>> > ,vchLastName AS [LastName]
>> > ,vchTitleDesc AS [Title]
>> > FROM fl_listgenexport
>> > WHERE iExportid = @iExportid
>> >
>> > It also returns back in 6 ms. If I add even one more field, it immediately
>> > jumps back up to 30 seconds.
>> >
>> > Not sure where or what to look at with the client stats.
>> >
>> >
>> >
>> > "Louis Davidson" wrote:
>> >
>> >> Check / post the plan for each query. There might be a big difference. If
>> >> there is not a big difference between the queries at a plan level, try doing
>> >> something like:
>> >>
>> >> select count(*)
>> >> from ( your query) as test
>> >>
>> >> This is a trick I use occasionally with big queries to see how quickly
>> >> things will run without having to return actual data.
>> >>
>> >> Then would look at the Client Statistics to see what might be happening. It
>> >> might be that you are hitting some threshold of your networking.
>> >>
>> >> Either way,check this stuff and post back. This is pretty interesting and
>> >> hopefully something you can find evidence of what is different in these
>> >> places/
>> >>
>> >> --
>> >> ----------------------------------------------------------------------------
>> >> Louis Davidson - http://spaces.msn.com/members/drsql/
>> >> SQL Server MVP
>> >>
>> >>
>> >> "jmeyers" <jmey***@discussions.microsoft.com> wrote in message
>> >> news:B9F52B4F-58B4-4084-ADC2-D29909237A47@microsoft.com...
>> >> > I've got a query that does a select of specific fields from a table. I'm
>> >> > finding that when I trim down the record length to approx 793 bytes, the
>> >> > query returns approx. 14,000 records in 1 second. When I go over the 793
>> >> > bytes, the query (returning the same 14,000 records) takes about 30
>> >> > seconds.
>> >> >
>> >> > Is there anything I can do to gain speed when the record length is more
>> >> > than
>> >> > 793 bytes? Here's the query:
>> >> >
>> >> > SELECT iTrackingId AS [TrackingId]
>> >> > ,iActionId AS [ActionId]
>> >> > ,dtActionDate AS [ActionDate]
>> >> > ,iIndividualId AS [IndId]
>> >> > ,iReferenceID AS [RefId]
>> >> > ,iEncryptedIndividualId AS [EncryptId]
>> >> > ,vchFirstName AS [FirstName]
>> >> > ,vchLastName AS [LastName]
>> >> > ,vchTitleDesc AS [Title]
>> >> > ,vchAddress1 AS [Address1]
>> >> > ,vchAddress2 AS [Address2]
>> >> > ,vchAddress3 AS [Address3]
>> >> > ,vchCity AS [City]
>> >> > ,chIndRegionName AS [State]
>> >> > ,vchPostCode AS [Zip]
>> >> > ,chIndCountryDesc AS [Country]
>> >> > ,vchEmailAddress AS [Email]
>> >> > ,vchPhoneNumber AS [Phone]
>> >> > ,iCompanyId AS [CompanyId]
>> >> > ,vchCompanyName AS [Company]
>> >> > ,chCompRegionName AS [CmpState]
>> >> > ,chCompCountryDesc AS [CmpCountry]
>> >> > ,vchCompanyType AS [CmpType]
>> >> > ,vchCompanySubType AS [CmpSubType]
>> >> > ,vchCompanyStatus AS [CmpStatus]
>> >> > ,vchMarketSector AS [SlsChnl]
>> >> > ,vchSIC AS [SIC]
>> >> > ,vchSICDescription AS [SICDesc]
>> >> > ,vchSpecificSIC AS [SpecSIC]
>> >> > ,chFieldRep AS [SalesRep]
>> >> > ,chAccountMgr AS [ISR]
>> >> > ,chDTMRep AS [DTMRep]
>> >> > ,chLeadCenterRep AS [LeadCntRep]
>> >> > FROM fl_listgenexport
>> >> > WHERE iExportid = @iExportid
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>

22 Aug 2005 3:10 PM

jmeyers

Yes, I did check the execution plan but to be honest, it's not any different
with the exception of the estimated row size. The CPU time for the query, no
matter how I run it, is approx. 3 ms, the elapsed time is where I'm seeing
the speed issue.

Show quote

"Tibor Karaszi" wrote:

> Did you check the execution plan?
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
>
> "jmeyers" <jmey***@discussions.microsoft.com> wrote in message
> news:50AAF174-CABD-4383-A018-992A9322BDF0@microsoft.com...
> > The only index on the table is clustered on the iExportId.
> >
> > "Tibor Karaszi" wrote:
> >
> >> Possibly, as you remove columns from the SELECT, you end up with a covered query (all columns
> >> participates in a non-clustered index). If such a case, all data is found in the index and non
> >> data
> >> page access is needed.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "jmeyers" <jmey***@discussions.microsoft.com> wrote in message
> >> news:D482206E-E636-47A6-9A91-4EFE43CA2310@microsoft.com...
> >> >I did as you suggested:
> >> >
> >> > select count(*) from ( your query) as test
> >> >
> >> > It cam back as 6 ms. In the query posted below, when I say 'trim down' the
> >> > select, specifically, if I run this query:
> >> >
> >> > SELECT iTrackingId AS [TrackingId]
> >> > ,iActionId AS [ActionId]
> >> > ,dtActionDate AS [ActionDate]
> >> > ,iIndividualId AS [IndId]
> >> > ,iReferenceID AS [RefId]
> >> > ,iEncryptedIndividualId AS [EncryptId]
> >> > ,vchFirstName AS [FirstName]
> >> > ,vchLastName AS [LastName]
> >> > ,vchTitleDesc AS [Title]
> >> > FROM fl_listgenexport
> >> > WHERE iExportid = @iExportid
> >> >
> >> > It also returns back in 6 ms. If I add even one more field, it immediately
> >> > jumps back up to 30 seconds.
> >> >
> >> > Not sure where or what to look at with the client stats.
> >> >
> >> >
> >> >
> >> > "Louis Davidson" wrote:
> >> >
> >> >> Check / post the plan for each query. There might be a big difference. If
> >> >> there is not a big difference between the queries at a plan level, try doing
> >> >> something like:
> >> >>
> >> >> select count(*)
> >> >> from ( your query) as test
> >> >>
> >> >> This is a trick I use occasionally with big queries to see how quickly
> >> >> things will run without having to return actual data.
> >> >>
> >> >> Then would look at the Client Statistics to see what might be happening. It
> >> >> might be that you are hitting some threshold of your networking.
> >> >>
> >> >> Either way,check this stuff and post back. This is pretty interesting and
> >> >> hopefully something you can find evidence of what is different in these
> >> >> places/
> >> >>
> >> >> --
> >> >> ----------------------------------------------------------------------------
> >> >> Louis Davidson - http://spaces.msn.com/members/drsql/
> >> >> SQL Server MVP
> >> >>
> >> >>
> >> >> "jmeyers" <jmey***@discussions.microsoft.com> wrote in message
> >> >> news:B9F52B4F-58B4-4084-ADC2-D29909237A47@microsoft.com...
> >> >> > I've got a query that does a select of specific fields from a table. I'm
> >> >> > finding that when I trim down the record length to approx 793 bytes, the
> >> >> > query returns approx. 14,000 records in 1 second. When I go over the 793
> >> >> > bytes, the query (returning the same 14,000 records) takes about 30
> >> >> > seconds.
> >> >> >
> >> >> > Is there anything I can do to gain speed when the record length is more
> >> >> > than
> >> >> > 793 bytes? Here's the query:
> >> >> >
> >> >> > SELECT iTrackingId AS [TrackingId]
> >> >> > ,iActionId AS [ActionId]
> >> >> > ,dtActionDate AS [ActionDate]
> >> >> > ,iIndividualId AS [IndId]
> >> >> > ,iReferenceID AS [RefId]
> >> >> > ,iEncryptedIndividualId AS [EncryptId]
> >> >> > ,vchFirstName AS [FirstName]
> >> >> > ,vchLastName AS [LastName]
> >> >> > ,vchTitleDesc AS [Title]
> >> >> > ,vchAddress1 AS [Address1]
> >> >> > ,vchAddress2 AS [Address2]
> >> >> > ,vchAddress3 AS [Address3]
> >> >> > ,vchCity AS [City]
> >> >> > ,chIndRegionName AS [State]
> >> >> > ,vchPostCode AS [Zip]
> >> >> > ,chIndCountryDesc AS [Country]
> >> >> > ,vchEmailAddress AS [Email]
> >> >> > ,vchPhoneNumber AS [Phone]
> >> >> > ,iCompanyId AS [CompanyId]
> >> >> > ,vchCompanyName AS [Company]
> >> >> > ,chCompRegionName AS [CmpState]
> >> >> > ,chCompCountryDesc AS [CmpCountry]
> >> >> > ,vchCompanyType AS [CmpType]
> >> >> > ,vchCompanySubType AS [CmpSubType]
> >> >> > ,vchCompanyStatus AS [CmpStatus]
> >> >> > ,vchMarketSector AS [SlsChnl]
> >> >> > ,vchSIC AS [SIC]
> >> >> > ,vchSICDescription AS [SICDesc]
> >> >> > ,vchSpecificSIC AS [SpecSIC]
> >> >> > ,chFieldRep AS [SalesRep]
> >> >> > ,chAccountMgr AS [ISR]
> >> >> > ,chDTMRep AS [DTMRep]
> >> >> > ,chLeadCenterRep AS [LeadCntRep]
> >> >> > FROM fl_listgenexport
> >> >> > WHERE iExportid = @iExportid
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
>
>

22 Aug 2005 3:24 PM

Tibor Karaszi

So by adding just one column, the query time is increased very much, without getting a different
execution plan?
Can this be any column? Did you tests having various column in the "quick" versions and also
variations in the slow version?

Can you reproduce this from Query Analyzer? Perhaps it is the client application which is slow in
consuming the rows? How many rows? If you already use query analyzer, did you try both text and grid
mode?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quote

"jmeyers" <jmey***@discussions.microsoft.com> wrote in message
news:F2D57542-B96B-4C75-8815-E233B0954455@microsoft.com...
> Yes, I did check the execution plan but to be honest, it's not any different
> with the exception of the estimated row size. The CPU time for the query, no
> matter how I run it, is approx. 3 ms, the elapsed time is where I'm seeing
> the speed issue.
>
> "Tibor Karaszi" wrote:
>
>> Did you check the execution plan?
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>>
>> "jmeyers" <jmey***@discussions.microsoft.com> wrote in message
>> news:50AAF174-CABD-4383-A018-992A9322BDF0@microsoft.com...
>> > The only index on the table is clustered on the iExportId.
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Possibly, as you remove columns from the SELECT, you end up with a covered query (all columns
>> >> participates in a non-clustered index). If such a case, all data is found in the index and non
>> >> data
>> >> page access is needed.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >>
>> >>
>> >> "jmeyers" <jmey***@discussions.microsoft.com> wrote in message
>> >> news:D482206E-E636-47A6-9A91-4EFE43CA2310@microsoft.com...
>> >> >I did as you suggested:
>> >> >
>> >> > select count(*) from ( your query) as test
>> >> >
>> >> > It cam back as 6 ms. In the query posted below, when I say 'trim down' the
>> >> > select, specifically, if I run this query:
>> >> >
>> >> > SELECT iTrackingId AS [TrackingId]
>> >> > ,iActionId AS [ActionId]
>> >> > ,dtActionDate AS [ActionDate]
>> >> > ,iIndividualId AS [IndId]
>> >> > ,iReferenceID AS [RefId]
>> >> > ,iEncryptedIndividualId AS [EncryptId]
>> >> > ,vchFirstName AS [FirstName]
>> >> > ,vchLastName AS [LastName]
>> >> > ,vchTitleDesc AS [Title]
>> >> > FROM fl_listgenexport
>> >> > WHERE iExportid = @iExportid
>> >> >
>> >> > It also returns back in 6 ms. If I add even one more field, it immediately
>> >> > jumps back up to 30 seconds.
>> >> >
>> >> > Not sure where or what to look at with the client stats.
>> >> >
>> >> >
>> >> >
>> >> > "Louis Davidson" wrote:
>> >> >
>> >> >> Check / post the plan for each query. There might be a big difference. If
>> >> >> there is not a big difference between the queries at a plan level, try doing
>> >> >> something like:
>> >> >>
>> >> >> select count(*)
>> >> >> from ( your query) as test
>> >> >>
>> >> >> This is a trick I use occasionally with big queries to see how quickly
>> >> >> things will run without having to return actual data.
>> >> >>
>> >> >> Then would look at the Client Statistics to see what might be happening. It
>> >> >> might be that you are hitting some threshold of your networking.
>> >> >>
>> >> >> Either way,check this stuff and post back. This is pretty interesting and
>> >> >> hopefully something you can find evidence of what is different in these
>> >> >> places/
>> >> >>
>> >> >> --
>> >> >> ----------------------------------------------------------------------------
>> >> >> Louis Davidson - http://spaces.msn.com/members/drsql/
>> >> >> SQL Server MVP
>> >> >>
>> >> >>
>> >> >> "jmeyers" <jmey***@discussions.microsoft.com> wrote in message
>> >> >> news:B9F52B4F-58B4-4084-ADC2-D29909237A47@microsoft.com...
>> >> >> > I've got a query that does a select of specific fields from a table. I'm
>> >> >> > finding that when I trim down the record length to approx 793 bytes, the
>> >> >> > query returns approx. 14,000 records in 1 second. When I go over the 793
>> >> >> > bytes, the query (returning the same 14,000 records) takes about 30
>> >> >> > seconds.
>> >> >> >
>> >> >> > Is there anything I can do to gain speed when the record length is more
>> >> >> > than
>> >> >> > 793 bytes? Here's the query:
>> >> >> >
>> >> >> > SELECT iTrackingId AS [TrackingId]
>> >> >> > ,iActionId AS [ActionId]
>> >> >> > ,dtActionDate AS [ActionDate]
>> >> >> > ,iIndividualId AS [IndId]
>> >> >> > ,iReferenceID AS [RefId]
>> >> >> > ,iEncryptedIndividualId AS [EncryptId]
>> >> >> > ,vchFirstName AS [FirstName]
>> >> >> > ,vchLastName AS [LastName]
>> >> >> > ,vchTitleDesc AS [Title]
>> >> >> > ,vchAddress1 AS [Address1]
>> >> >> > ,vchAddress2 AS [Address2]
>> >> >> > ,vchAddress3 AS [Address3]
>> >> >> > ,vchCity AS [City]
>> >> >> > ,chIndRegionName AS [State]
>> >> >> > ,vchPostCode AS [Zip]
>> >> >> > ,chIndCountryDesc AS [Country]
>> >> >> > ,vchEmailAddress AS [Email]
>> >> >> > ,vchPhoneNumber AS [Phone]
>> >> >> > ,iCompanyId AS [CompanyId]
>> >> >> > ,vchCompanyName AS [Company]
>> >> >> > ,chCompRegionName AS [CmpState]
>> >> >> > ,chCompCountryDesc AS [CmpCountry]
>> >> >> > ,vchCompanyType AS [CmpType]
>> >> >> > ,vchCompanySubType AS [CmpSubType]
>> >> >> > ,vchCompanyStatus AS [CmpStatus]
>> >> >> > ,vchMarketSector AS [SlsChnl]
>> >> >> > ,vchSIC AS [SIC]
>> >> >> > ,vchSICDescription AS [SICDesc]
>> >> >> > ,vchSpecificSIC AS [SpecSIC]
>> >> >> > ,chFieldRep AS [SalesRep]
>> >> >> > ,chAccountMgr AS [ISR]
>> >> >> > ,chDTMRep AS [DTMRep]
>> >> >> > ,chLeadCenterRep AS [LeadCntRep]
>> >> >> > FROM fl_listgenexport
>> >> >> > WHERE iExportid = @iExportid
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>>
>>

22 Aug 2005 3:34 PM

it might help to
set statistics profile on

and see what that elapsed time consists of

22 Aug 2005 3:34 PM

jmeyers

Yep - by adding just one column causes the time increase. I'm currently in
query analyzer - the UI it's attached to is a browser based CRM package and
it will time out. So, I've been working with it in query analyzer in text
mode. I just now tested in grid mode and the time increase is gone. What's
up with that?

Show quote

"Tibor Karaszi" wrote:

> So by adding just one column, the query time is increased very much, without getting a different
> execution plan?
> Can this be any column? Did you tests having various column in the "quick" versions and also
> variations in the slow version?
>
> Can you reproduce this from Query Analyzer? Perhaps it is the client application which is slow in
> consuming the rows? How many rows? If you already use query analyzer, did you try both text and grid
> mode?
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
>
> "jmeyers" <jmey***@discussions.microsoft.com> wrote in message
> news:F2D57542-B96B-4C75-8815-E233B0954455@microsoft.com...
> > Yes, I did check the execution plan but to be honest, it's not any different
> > with the exception of the estimated row size. The CPU time for the query, no
> > matter how I run it, is approx. 3 ms, the elapsed time is where I'm seeing
> > the speed issue.
> >
> > "Tibor Karaszi" wrote:
> >
> >> Did you check the execution plan?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "jmeyers" <jmey***@discussions.microsoft.com> wrote in message
> >> news:50AAF174-CABD-4383-A018-992A9322BDF0@microsoft.com...
> >> > The only index on the table is clustered on the iExportId.
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Possibly, as you remove columns from the SELECT, you end up with a covered query (all columns
> >> >> participates in a non-clustered index). If such a case, all data is found in the index and non
> >> >> data
> >> >> page access is needed.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >> >>
> >> >>
> >> >> "jmeyers" <jmey***@discussions.microsoft.com> wrote in message
> >> >> news:D482206E-E636-47A6-9A91-4EFE43CA2310@microsoft.com...
> >> >> >I did as you suggested:
> >> >> >
> >> >> > select count(*) from ( your query) as test
> >> >> >
> >> >> > It cam back as 6 ms. In the query posted below, when I say 'trim down' the
> >> >> > select, specifically, if I run this query:
> >> >> >
> >> >> > SELECT iTrackingId AS [TrackingId]
> >> >> > ,iActionId AS [ActionId]
> >> >> > ,dtActionDate AS [ActionDate]
> >> >> > ,iIndividualId AS [IndId]
> >> >> > ,iReferenceID AS [RefId]
> >> >> > ,iEncryptedIndividualId AS [EncryptId]
> >> >> > ,vchFirstName AS [FirstName]
> >> >> > ,vchLastName AS [LastName]
> >> >> > ,vchTitleDesc AS [Title]
> >> >> > FROM fl_listgenexport
> >> >> > WHERE iExportid = @iExportid
> >> >> >
> >> >> > It also returns back in 6 ms. If I add even one more field, it immediately
> >> >> > jumps back up to 30 seconds.
> >> >> >
> >> >> > Not sure where or what to look at with the client stats.
> >> >> >
> >> >> >
> >> >> >
> >> >> > "Louis Davidson" wrote:
> >> >> >
> >> >> >> Check / post the plan for each query. There might be a big difference. If
> >> >> >> there is not a big difference between the queries at a plan level, try doing
> >> >> >> something like:
> >> >> >>
> >> >> >> select count(*)
> >> >> >> from ( your query) as test
> >> >> >>
> >> >> >> This is a trick I use occasionally with big queries to see how quickly
> >> >> >> things will run without having to return actual data.
> >> >> >>
> >> >> >> Then would look at the Client Statistics to see what might be happening. It
> >> >> >> might be that you are hitting some threshold of your networking.
> >> >> >>
> >> >> >> Either way,check this stuff and post back. This is pretty interesting and
> >> >> >> hopefully something you can find evidence of what is different in these
> >> >> >> places/
> >> >> >>
> >> >> >> --
> >> >> >> ----------------------------------------------------------------------------
> >> >> >> Louis Davidson - http://spaces.msn.com/members/drsql/
> >> >> >> SQL Server MVP
> >> >> >>
> >> >> >>
> >> >> >> "jmeyers" <jmey***@discussions.microsoft.com> wrote in message
> >> >> >> news:B9F52B4F-58B4-4084-ADC2-D29909237A47@microsoft.com...
> >> >> >> > I've got a query that does a select of specific fields from a table. I'm
> >> >> >> > finding that when I trim down the record length to approx 793 bytes, the
> >> >> >> > query returns approx. 14,000 records in 1 second. When I go over the 793
> >> >> >> > bytes, the query (returning the same 14,000 records) takes about 30
> >> >> >> > seconds.
> >> >> >> >
> >> >> >> > Is there anything I can do to gain speed when the record length is more
> >> >> >> > than
> >> >> >> > 793 bytes? Here's the query:
> >> >> >> >
> >> >> >> > SELECT iTrackingId AS [TrackingId]
> >> >> >> > ,iActionId AS [ActionId]
> >> >> >> > ,dtActionDate AS [ActionDate]
> >> >> >> > ,iIndividualId AS [IndId]
> >> >> >> > ,iReferenceID AS [RefId]
> >> >> >> > ,iEncryptedIndividualId AS [EncryptId]
> >> >> >> > ,vchFirstName AS [FirstName]
> >> >> >> > ,vchLastName AS [LastName]
> >> >> >> > ,vchTitleDesc AS [Title]
> >> >> >> > ,vchAddress1 AS [Address1]
> >> >> >> > ,vchAddress2 AS [Address2]
> >> >> >> > ,vchAddress3 AS [Address3]
> >> >> >> > ,vchCity AS [City]
> >> >> >> > ,chIndRegionName AS [State]
> >> >> >> > ,vchPostCode AS [Zip]
> >> >> >> > ,chIndCountryDesc AS [Country]
> >> >> >> > ,vchEmailAddress AS [Email]
> >> >> >> > ,vchPhoneNumber AS [Phone]
> >> >> >> > ,iCompanyId AS [CompanyId]
> >> >> >> > ,vchCompanyName AS [Company]
> >> >> >> > ,chCompRegionName AS [CmpState]
> >> >> >> > ,chCompCountryDesc AS [CmpCountry]
> >> >> >> > ,vchCompanyType AS [CmpType]
> >> >> >> > ,vchCompanySubType AS [CmpSubType]
> >> >> >> > ,vchCompanyStatus AS [CmpStatus]
> >> >> >> > ,vchMarketSector AS [SlsChnl]
> >> >> >> > ,vchSIC AS [SIC]
> >> >> >> > ,vchSICDescription AS [SICDesc]
> >> >> >> > ,vchSpecificSIC AS [SpecSIC]
> >> >> >> > ,chFieldRep AS [SalesRep]
> >> >> >> > ,chAccountMgr AS [ISR]
> >> >> >> > ,chDTMRep AS [DTMRep]
> >> >> >> > ,chLeadCenterRep AS [LeadCntRep]
> >> >> >> > FROM fl_listgenexport
> >> >> >> > WHERE iExportid = @iExportid
> >> >> >> >
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >>
> >>
>
>

22 Aug 2005 3:43 PM

Tibor Karaszi

Text mode is slower as it has to "paint"/fill white spaces, like spaces and tabs.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quote

"jmeyers" <jmey***@discussions.microsoft.com> wrote in message
news:201F59AE-F1A3-4BEA-9F0D-413FDC6EB42D@microsoft.com...
> Yep - by adding just one column causes the time increase. I'm currently in
> query analyzer - the UI it's attached to is a browser based CRM package and
> it will time out. So, I've been working with it in query analyzer in text
> mode. I just now tested in grid mode and the time increase is gone. What's
> up with that?
>
> "Tibor Karaszi" wrote:
>
>> So by adding just one column, the query time is increased very much, without getting a different
>> execution plan?
>> Can this be any column? Did you tests having various column in the "quick" versions and also
>> variations in the slow version?
>>
>> Can you reproduce this from Query Analyzer? Perhaps it is the client application which is slow in
>> consuming the rows? How many rows? If you already use query analyzer, did you try both text and
>> grid
>> mode?
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>>
>> "jmeyers" <jmey***@discussions.microsoft.com> wrote in message
>> news:F2D57542-B96B-4C75-8815-E233B0954455@microsoft.com...
>> > Yes, I did check the execution plan but to be honest, it's not any different
>> > with the exception of the estimated row size. The CPU time for the query, no
>> > matter how I run it, is approx. 3 ms, the elapsed time is where I'm seeing
>> > the speed issue.
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Did you check the execution plan?
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >>
>> >>
>> >> "jmeyers" <jmey***@discussions.microsoft.com> wrote in message
>> >> news:50AAF174-CABD-4383-A018-992A9322BDF0@microsoft.com...
>> >> > The only index on the table is clustered on the iExportId.
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> Possibly, as you remove columns from the SELECT, you end up with a covered query (all
>> >> >> columns
>> >> >> participates in a non-clustered index). If such a case, all data is found in the index and
>> >> >> non
>> >> >> data
>> >> >> page access is needed.
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://www.solidqualitylearning.com/
>> >> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >> >>
>> >> >>
>> >> >> "jmeyers" <jmey***@discussions.microsoft.com> wrote in message
>> >> >> news:D482206E-E636-47A6-9A91-4EFE43CA2310@microsoft.com...
>> >> >> >I did as you suggested:
>> >> >> >
>> >> >> > select count(*) from ( your query) as test
>> >> >> >
>> >> >> > It cam back as 6 ms. In the query posted below, when I say 'trim down' the
>> >> >> > select, specifically, if I run this query:
>> >> >> >
>> >> >> > SELECT iTrackingId AS [TrackingId]
>> >> >> > ,iActionId AS [ActionId]
>> >> >> > ,dtActionDate AS [ActionDate]
>> >> >> > ,iIndividualId AS [IndId]
>> >> >> > ,iReferenceID AS [RefId]
>> >> >> > ,iEncryptedIndividualId AS [EncryptId]
>> >> >> > ,vchFirstName AS [FirstName]
>> >> >> > ,vchLastName AS [LastName]
>> >> >> > ,vchTitleDesc AS [Title]
>> >> >> > FROM fl_listgenexport
>> >> >> > WHERE iExportid = @iExportid
>> >> >> >
>> >> >> > It also returns back in 6 ms. If I add even one more field, it immediately
>> >> >> > jumps back up to 30 seconds.
>> >> >> >
>> >> >> > Not sure where or what to look at with the client stats.
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> > "Louis Davidson" wrote:
>> >> >> >
>> >> >> >> Check / post the plan for each query. There might be a big difference. If
>> >> >> >> there is not a big difference between the queries at a plan level, try doing
>> >> >> >> something like:
>> >> >> >>
>> >> >> >> select count(*)
>> >> >> >> from ( your query) as test
>> >> >> >>
>> >> >> >> This is a trick I use occasionally with big queries to see how quickly
>> >> >> >> things will run without having to return actual data.
>> >> >> >>
>> >> >> >> Then would look at the Client Statistics to see what might be happening. It
>> >> >> >> might be that you are hitting some threshold of your networking.
>> >> >> >>
>> >> >> >> Either way,check this stuff and post back. This is pretty interesting and
>> >> >> >> hopefully something you can find evidence of what is different in these
>> >> >> >> places/
>> >> >> >>
>> >> >> >> --
>> >> >> >> ----------------------------------------------------------------------------
>> >> >> >> Louis Davidson - http://spaces.msn.com/members/drsql/
>> >> >> >> SQL Server MVP
>> >> >> >>
>> >> >> >>
>> >> >> >> "jmeyers" <jmey***@discussions.microsoft.com> wrote in message
>> >> >> >> news:B9F52B4F-58B4-4084-ADC2-D29909237A47@microsoft.com...
>> >> >> >> > I've got a query that does a select of specific fields from a table. I'm
>> >> >> >> > finding that when I trim down the record length to approx 793 bytes, the
>> >> >> >> > query returns approx. 14,000 records in 1 second. When I go over the 793
>> >> >> >> > bytes, the query (returning the same 14,000 records) takes about 30
>> >> >> >> > seconds.
>> >> >> >> >
>> >> >> >> > Is there anything I can do to gain speed when the record length is more
>> >> >> >> > than
>> >> >> >> > 793 bytes? Here's the query:
>> >> >> >> >
>> >> >> >> > SELECT iTrackingId AS [TrackingId]
>> >> >> >> > ,iActionId AS [ActionId]
>> >> >> >> > ,dtActionDate AS [ActionDate]
>> >> >> >> > ,iIndividualId AS [IndId]
>> >> >> >> > ,iReferenceID AS [RefId]
>> >> >> >> > ,iEncryptedIndividualId AS [EncryptId]
>> >> >> >> > ,vchFirstName AS [FirstName]
>> >> >> >> > ,vchLastName AS [LastName]
>> >> >> >> > ,vchTitleDesc AS [Title]
>> >> >> >> > ,vchAddress1 AS [Address1]
>> >> >> >> > ,vchAddress2 AS [Address2]
>> >> >> >> > ,vchAddress3 AS [Address3]
>> >> >> >> > ,vchCity AS [City]
>> >> >> >> > ,chIndRegionName AS [State]
>> >> >> >> > ,vchPostCode AS [Zip]
>> >> >> >> > ,chIndCountryDesc AS [Country]
>> >> >> >> > ,vchEmailAddress AS [Email]
>> >> >> >> > ,vchPhoneNumber AS [Phone]
>> >> >> >> > ,iCompanyId AS [CompanyId]
>> >> >> >> > ,vchCompanyName AS [Company]
>> >> >> >> > ,chCompRegionName AS [CmpState]
>> >> >> >> > ,chCompCountryDesc AS [CmpCountry]
>> >> >> >> > ,vchCompanyType AS [CmpType]
>> >> >> >> > ,vchCompanySubType AS [CmpSubType]
>> >> >> >> > ,vchCompanyStatus AS [CmpStatus]
>> >> >> >> > ,vchMarketSector AS [SlsChnl]
>> >> >> >> > ,vchSIC AS [SIC]
>> >> >> >> > ,vchSICDescription AS [SICDesc]
>> >> >> >> > ,vchSpecificSIC AS [SpecSIC]
>> >> >> >> > ,chFieldRep AS [SalesRep]
>> >> >> >> > ,chAccountMgr AS [ISR]
>> >> >> >> > ,chDTMRep AS [DTMRep]
>> >> >> >> > ,chLeadCenterRep AS [LeadCntRep]
>> >> >> >> > FROM fl_listgenexport
>> >> >> >> > WHERE iExportid = @iExportid
>> >> >> >> >
>> >> >> >> >
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>>
>>