Re: How can I assign the result of dateadd to a variable ?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Paul fpvt2 (Paulfpvt2_at_discussions.microsoft.com)
Date: 12/14/04 

Date: Tue, 14 Dec 2004 15:47:03 -0800

Thank you very much for your reply.
I will find out the average length of packet_data and the maximum length,
and I will post it here.
The reason why I used text is because the length of the packet_data varies,
and at this point I don't know what is the maximum length. Based on the
current data we have, I will write a program to find out what is the maximum
length of packet_Data.

What is text in row option ?

Thanks a lot.

"Hugo Kornelis" wrote:

> On Tue, 14 Dec 2004 09:49:01 -0800, Paul fpvt2 wrote:
>
> >On any of the following queries, the result is returned in 0 seconds.
> >select count(*) from packet where packet_time >= dateadd(hour, -5,
> >getdate()) and packet_contract = 'ABCD'
> >OR
> >select count(*) from packet where packet_time >= dateadd(hour, -12,
> >getdate()) and packet_contract = 'ABCD'
> >OR
> >select count(*) from packet where packet_time >= dateadd(hour, -96,
> >getdate()) and packet_contract = 'ABCD'
>
> Hi Paul,
>
> Well, I guess you can't complain about those response times :-)
>
> (snip)
> >select packet_data INTO #tmptable1 from packet where packet_time >=
> >dateadd(hour, -5, getdate()) and packet_contract = 'ABCD'
> >--> Takes 12 seconds compare to 20 seconds if I do the following query:
> >select packet_data from packet where packet_time >= dateadd(hour, -5,
> >getdate()) and packet_contract = 'ABCD'
> >The query above returns 19,500 records
> (snip)
> >Are the difference in time in the above queries due to format it for display
> >and put it on the SQL Query analyzer screen ?
>
> Quite probably, yes.
>
> What bugged me more was the difference in execution time between the query
> with INTO #tmptable and the COUNT(*) query. I didn't understand it, until
> I re-read your original post with the table definition. Until now, I
> somehow failed to notice that packet_data is defined as text. This will
> slow down the kind of queries you are now using.
>
> For a text column, the data pages (the leaf pages of the clustered index)
> will only store a locator. The actual data is stored elsewhere in the
> database, on as many pages as needed. This means that your above query,
> returning 19,500 rows, had to do at least 19,500 extra page reads from
> different locations in the database.
>
> Do you really need to use text as datatype for packet_data? Maybe you
> could get better results by switching to varchar (or even char). Or by
> using the text in row option. But that would depend on the answers to
> these questions: What is the average length of packet_data? And what is
> the maximum length?
>
>
> >Since I call the Stored Procedure from a VB program using ADO, will the time
> >takes to execute the query
> >and returing the result the same with selecting into the #tmptable or will
> >it format it for display also ?
>
> I know precious little about ADO and VB, but if you select INTO #tmptable,
> there will be no rows returned to the client, so the client will have
> nothing to format (in fact, that was the only reason I asked you to add
> the INTO clause - this was not intended as a permanent change to your
> code).
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Relevant Pages

  • Re: Multiple PHone Numbers
    ... possible to make a query that puts the name, ... after it on the same line - for this reason I have two tables - client ...
    (comp.databases.ms-access)
  • Re: How can I assign the result of dateadd to a variable ?
    ... I just try to do the following query: ... Total number of records in the database: ... Will changing the packet_data column to either varchar or text in row option ... > there will be no rows returned to the client, ...
    (microsoft.public.sqlserver.programming)
  • Re: AND in query expression
    ... For some reason if I replace the FIELD Z and TABLE CLIENT in the query ... OfficeDev18 wrote: ...
    (microsoft.public.access.queries)
  • RE: Query Problem, Please Help!!
    ... > this table to be laid out the way it is. ... The reason that experts recommend using a query as the Record Source for a ... The query ... will allow the designer to display the data in just about any way needed. ...
    (microsoft.public.access.queries)
  • Re: Issue with order placement...
    ... David's suggestion to use Me.Dirty = False is a good one. ... variable again to re-execute the same query, but I can also tell you this -- ... executing the identical update query to set a field to zero twice in a row ... There is good reason to set ...
    (comp.databases.ms-access)