Footer rows to skip: time

Showing posts with label time. Show all posts

Monday, March 26, 2012

Fetch limited rows sequentially

Hello Friends,
I want to fetch limited rows from single query (example:-1000 rows present in emp table , I want to fetch first 10 rows at a time and store in a file. Next iteration it has to fetch next 10 rows and store in another file like this it has to create 10 different files with 10 rows)
Thanks in advance
Waiting early reply
Regds
NitinHi Nitin,

SELECT *
FROM (SELECT TOP 10 *
FROM (SELECT TOP 20 *
FROM emp
ORDER BY LName ASC) AS t1
ORDER BY LName DESC) as t2
ORDER BY LName

This select will return the 10 after the first ten from emps table. Just loop through increasing the TOP 20 to TOP 30 and so on to get each interval of ten employers.

Hope this helps! :)
Robert

Wednesday, March 21, 2012

Fatal Exception in SQL Server

Hello,
We got a SQL Server (no SP) running on Win 2k SP4. Recently been getting
this error message in event viewer and at the same time the program that
access SQL Server crashes.
Error: 0, Severity: 19, State: 0
SqlDumpExceptionHandler: Process 64 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Does anyone have any idea what it is please. Thanks
Andrew> We got a SQL Server (no SP) running on Win 2k SP4.
If this is SQL2000, apply SQL2000 sevice pack 4 + AWE memory hotfix first.
If this SQL2005, open a support case with Microsoft. But I'd strongly
suggest that you run SQL2005 on Windows 2003 SP1.
Linchi
"Huy (Andrew) V Nguyen" <andrew.nv@.gmail.com> wrote in message
news:%234ZSg%23cLGHA.3756@.TK2MSFTNGP10.phx.gbl...
> Hello,
> We got a SQL Server (no SP) running on Win 2k SP4. Recently been getting
> this error message in event viewer and at the same time the program that
> access SQL Server crashes.
> Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 64 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Does anyone have any idea what it is please. Thanks
> Andrew|||Thank you for your reply. I already applied SP4 still the same message.
Where can I obtain AWE hotfix please.
Andrew
Linchi Shea wrote:
>> We got a SQL Server (no SP) running on Win 2k SP4.
> If this is SQL2000, apply SQL2000 sevice pack 4 + AWE memory hotfix first.
> If this SQL2005, open a support case with Microsoft. But I'd strongly
> suggest that you run SQL2005 on Windows 2003 SP1.
> Linchi
> "Huy (Andrew) V Nguyen" <andrew.nv@.gmail.com> wrote in message
> news:%234ZSg%23cLGHA.3756@.TK2MSFTNGP10.phx.gbl...
>> Hello,
>> We got a SQL Server (no SP) running on Win 2k SP4. Recently been getting
>> this error message in event viewer and at the same time the program that
>> access SQL Server crashes.
>> Error: 0, Severity: 19, State: 0
>> SqlDumpExceptionHandler: Process 64 generated fatal exception c0000005
>> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
>> Does anyone have any idea what it is please. Thanks
>> Andrew
>|||You can get the AWE fix here :
http://www.microsoft.com/downloads/details.aspx?FamilyId=7C407047-3F1F-48B8-9E4C-DC32875E1961&displaylang=en
But you will need to log a case with Microsoft CSS , because the error you
posted is not sufficient enough , we need at least the stack output from the
AV , and if there was any mini dump created in the log folder during this
time...
So as linchi said please log a case with Microsoft CSS for speedy resolution
HTH
"Huy (Andrew) V Nguyen" <andrew.nv@.gmail.com> wrote in message
news:uVHDk9cMGHA.3264@.TK2MSFTNGP11.phx.gbl...
> Thank you for your reply. I already applied SP4 still the same message.
> Where can I obtain AWE hotfix please.
> Andrew
> Linchi Shea wrote:
>> We got a SQL Server (no SP) running on Win 2k SP4.
>> If this is SQL2000, apply SQL2000 sevice pack 4 + AWE memory hotfix
>> first. If this SQL2005, open a support case with Microsoft. But I'd
>> strongly suggest that you run SQL2005 on Windows 2003 SP1.
>> Linchi
>> "Huy (Andrew) V Nguyen" <andrew.nv@.gmail.com> wrote in message
>> news:%234ZSg%23cLGHA.3756@.TK2MSFTNGP10.phx.gbl...
>> Hello,
>> We got a SQL Server (no SP) running on Win 2k SP4. Recently been getting
>> this error message in event viewer and at the same time the program that
>> access SQL Server crashes.
>> Error: 0, Severity: 19, State: 0
>> SqlDumpExceptionHandler: Process 64 generated fatal exception c0000005
>> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
>> Does anyone have any idea what it is please. Thanks
>> Andrew

Monday, March 12, 2012

Fastest way to query Oracle

I have linked my Oracle9i database to SqlServer2000. When I query the Oracle
database
from Query Analyzer it is taking much longer time than if I do it from
Oracle directly.
Is there anything I can do make my queries run faster?Just a quick thought. See if you use OPENQUERY to make the query faster. See
SQL Server Books Online for more information and examples.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"ln54" <ln54@.discussions.microsoft.com> wrote in message
news:8AFAE7AE-BD27-43E7-B1E7-1B20600E44AE@.microsoft.com...
I have linked my Oracle9i database to SqlServer2000. When I query the Oracle
database
from Query Analyzer it is taking much longer time than if I do it from
Oracle directly.
Is there anything I can do make my queries run faster?|||ThankYou, this was much better,
also thanks for Your excellent script "searchalltables"
"Narayana Vyas Kondreddi" wrote:

> Just a quick thought. See if you use OPENQUERY to make the query faster. S
ee
> SQL Server Books Online for more information and examples.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "ln54" <ln54@.discussions.microsoft.com> wrote in message
> news:8AFAE7AE-BD27-43E7-B1E7-1B20600E44AE@.microsoft.com...
> I have linked my Oracle9i database to SqlServer2000. When I query the Orac
le
> database
> from Query Analyzer it is taking much longer time than if I do it from
> Oracle directly.
> Is there anything I can do make my queries run faster?
>
>

Fastest way to query Oracle

I have linked my Oracle9i database to SqlServer2000. When I query the Oracle
database
from Query Analyzer it is taking much longer time than if I do it from
Oracle directly.
Is there anything I can do make my queries run faster?
Just a quick thought. See if you use OPENQUERY to make the query faster. See
SQL Server Books Online for more information and examples.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"ln54" <ln54@.discussions.microsoft.com> wrote in message
news:8AFAE7AE-BD27-43E7-B1E7-1B20600E44AE@.microsoft.com...
I have linked my Oracle9i database to SqlServer2000. When I query the Oracle
database
from Query Analyzer it is taking much longer time than if I do it from
Oracle directly.
Is there anything I can do make my queries run faster?
|||ThankYou, this was much better,
also thanks for Your excellent script "searchalltables"
"Narayana Vyas Kondreddi" wrote:

> Just a quick thought. See if you use OPENQUERY to make the query faster. See
> SQL Server Books Online for more information and examples.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "ln54" <ln54@.discussions.microsoft.com> wrote in message
> news:8AFAE7AE-BD27-43E7-B1E7-1B20600E44AE@.microsoft.com...
> I have linked my Oracle9i database to SqlServer2000. When I query the Oracle
> database
> from Query Analyzer it is taking much longer time than if I do it from
> Oracle directly.
> Is there anything I can do make my queries run faster?
>
>

Fastest way to query Oracle

I have linked my Oracle9i database to SqlServer2000. When I query the Oracle
database
from Query Analyzer it is taking much longer time than if I do it from
Oracle directly.
Is there anything I can do make my queries run faster?Just a quick thought. See if you use OPENQUERY to make the query faster. See
SQL Server Books Online for more information and examples.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"ln54" <ln54@.discussions.microsoft.com> wrote in message
news:8AFAE7AE-BD27-43E7-B1E7-1B20600E44AE@.microsoft.com...
I have linked my Oracle9i database to SqlServer2000. When I query the Oracle
database
from Query Analyzer it is taking much longer time than if I do it from
Oracle directly.
Is there anything I can do make my queries run faster?|||ThankYou, this was much better,
also thanks for Your excellent script "searchalltables"
"Narayana Vyas Kondreddi" wrote:
> Just a quick thought. See if you use OPENQUERY to make the query faster. See
> SQL Server Books Online for more information and examples.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "ln54" <ln54@.discussions.microsoft.com> wrote in message
> news:8AFAE7AE-BD27-43E7-B1E7-1B20600E44AE@.microsoft.com...
> I have linked my Oracle9i database to SqlServer2000. When I query the Oracle
> database
> from Query Analyzer it is taking much longer time than if I do it from
> Oracle directly.
> Is there anything I can do make my queries run faster?
>
>

Fastest way to locate overlapping time blocks

Hello, I am searching for an absolutely fastest SQL to locate
overlapping time blocks. I've tried various things, but i am a c# guy,
and sql is not my strongest suit.
create table TimeBlocks
(
TimeBlockStartsAt datetime
TimeBlockEndsAt datetime
)
I want to find a condition where one row overlaps another one
(time-wise). I've tried looping through rows and comparing one by one,
but the table contains millions of rows and the procedure is slow.
Thanks.Try:
select
*
from
TimeBlocks t1
join
TimeBlocks t2 on (t1.TimeBlockStartsAt <= t2.TimeBlockStartsAt
and t1.TimeBlockEndsAt >= t2.TimeBlockStartsAt)
Also, create a clustered index on (TimeBlockStartsAt, TimeBlockStartsAt).
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Frank Rizzo" <none@.none.com> wrote in message
news:OQbUlSuyGHA.3464@.TK2MSFTNGP03.phx.gbl...
Hello, I am searching for an absolutely fastest SQL to locate
overlapping time blocks. I've tried various things, but i am a c# guy,
and sql is not my strongest suit.
create table TimeBlocks
(
TimeBlockStartsAt datetime
TimeBlockEndsAt datetime
)
I want to find a condition where one row overlaps another one
(time-wise). I've tried looping through rows and comparing one by one,
but the table contains millions of rows and the procedure is slow.
Thanks.|||Tom Moreau wrote:
> Try:
> select
> *
> from
> TimeBlocks t1
> join
> TimeBlocks t2 on (t1.TimeBlockStartsAt <= t2.TimeBlockStartsA
t
> and t1.TimeBlockEndsAt >= t2.TimeBlockStartsAt)
> Also, create a clustered index on (TimeBlockStartsAt, TimeBlockStartsAt).
>
Thanks, Tom. I've tried this (with a clustered index), but it is too
slow for the amount of records I have. Not sure whether SQL can come up
with something faster. I think I am gonna have to come with a
completely alternative solution for this problem.|||What might help is to have a primary key, perhaps an identity. Then you can
use nonclustered indexes on the time columns. Also, experiment with adding
a nonclustered on each time column, as well as on both columns together.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Frank Rizzo" <none@.none.com> wrote in message
news:%234q$mxuyGHA.4932@.TK2MSFTNGP02.phx.gbl...
Tom Moreau wrote:
> Try:
> select
> *
> from
> TimeBlocks t1
> join
> TimeBlocks t2 on (t1.TimeBlockStartsAt <=
> t2.TimeBlockStartsAt
> and t1.TimeBlockEndsAt >= t2.TimeBlockStartsAt)
> Also, create a clustered index on (TimeBlockStartsAt, TimeBlockStartsAt).
>
Thanks, Tom. I've tried this (with a clustered index), but it is too
slow for the amount of records I have. Not sure whether SQL can come up
with something faster. I think I am gonna have to come with a
completely alternative solution for this problem.|||On Mon, 28 Aug 2006 14:53:29 -0700, Frank Rizzo <none@.none.com> wrote:
>Thanks, Tom. I've tried this (with a clustered index), but it is too
>slow for the amount of records I have. Not sure whether SQL can come up
>with something faster. I think I am gonna have to come with a
>completely alternative solution for this problem.
Is that what you want, a list out of your zillion rows of which have
overlaps, or will you have a fixed interval you want to search for?
I've done a little of this (more second case), and it is likely to be
slow (esp the first case), even when you're all indexed.
Might do a little better if the first test is t1.timeblockstartsat is
between x and y, if your intervals are typically short you can compute
reasonable x and y as start-i and start+i.
J.|||JXStern wrote:
> On Mon, 28 Aug 2006 14:53:29 -0700, Frank Rizzo <none@.none.com> wrote:
> Is that what you want, a list out of your zillion rows of which have
> overlaps, or will you have a fixed interval you want to search for?
> I've done a little of this (more second case), and it is likely to be
> slow (esp the first case), even when you're all indexed.
> Might do a little better if the first test is t1.timeblockstartsat is
> between x and y, if your intervals are typically short you can compute
> reasonable x and y as start-i and start+i.
Thanks, that's what I found. I think I'll have to change gears and
record more information when I enter the row (e.g. check whether it
overlaps then) and mark a field or something to that effect.

Fastest way to locate overlapping time blocks

Hello, I am searching for an absolutely fastest SQL to locate
overlapping time blocks. I've tried various things, but i am a c# guy,
and sql is not my strongest suit.
create table TimeBlocks
(
TimeBlockStartsAt datetime
TimeBlockEndsAt datetime
)
I want to find a condition where one row overlaps another one
(time-wise). I've tried looping through rows and comparing one by one,
but the table contains millions of rows and the procedure is slow.
Thanks.Try:
select
*
from
TimeBlocks t1
join
TimeBlocks t2 on (t1.TimeBlockStartsAt <= t2.TimeBlockStartsAt
and t1.TimeBlockEndsAt >= t2.TimeBlockStartsAt)
Also, create a clustered index on (TimeBlockStartsAt, TimeBlockStartsAt).
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Frank Rizzo" <none@.none.com> wrote in message
news:OQbUlSuyGHA.3464@.TK2MSFTNGP03.phx.gbl...
Hello, I am searching for an absolutely fastest SQL to locate
overlapping time blocks. I've tried various things, but i am a c# guy,
and sql is not my strongest suit.
create table TimeBlocks
(
TimeBlockStartsAt datetime
TimeBlockEndsAt datetime
)
I want to find a condition where one row overlaps another one
(time-wise). I've tried looping through rows and comparing one by one,
but the table contains millions of rows and the procedure is slow.
Thanks.|||Tom Moreau wrote:
> Try:
> select
> *
> from
> TimeBlocks t1
> join
> TimeBlocks t2 on (t1.TimeBlockStartsAt <= t2.TimeBlockStartsAt
> and t1.TimeBlockEndsAt >= t2.TimeBlockStartsAt)
> Also, create a clustered index on (TimeBlockStartsAt, TimeBlockStartsAt).
>
Thanks, Tom. I've tried this (with a clustered index), but it is too
slow for the amount of records I have. Not sure whether SQL can come up
with something faster. I think I am gonna have to come with a
completely alternative solution for this problem.|||What might help is to have a primary key, perhaps an identity. Then you can
use nonclustered indexes on the time columns. Also, experiment with adding
a nonclustered on each time column, as well as on both columns together.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Frank Rizzo" <none@.none.com> wrote in message
news:%234q$mxuyGHA.4932@.TK2MSFTNGP02.phx.gbl...
Tom Moreau wrote:
> Try:
> select
> *
> from
> TimeBlocks t1
> join
> TimeBlocks t2 on (t1.TimeBlockStartsAt <=> t2.TimeBlockStartsAt
> and t1.TimeBlockEndsAt >= t2.TimeBlockStartsAt)
> Also, create a clustered index on (TimeBlockStartsAt, TimeBlockStartsAt).
>
Thanks, Tom. I've tried this (with a clustered index), but it is too
slow for the amount of records I have. Not sure whether SQL can come up
with something faster. I think I am gonna have to come with a
completely alternative solution for this problem.|||On Mon, 28 Aug 2006 14:53:29 -0700, Frank Rizzo <none@.none.com> wrote:
>> select
>> *
>> from
>> TimeBlocks t1
>> join
>> TimeBlocks t2 on (t1.TimeBlockStartsAt <= t2.TimeBlockStartsAt
>> and t1.TimeBlockEndsAt >= t2.TimeBlockStartsAt)
>> Also, create a clustered index on (TimeBlockStartsAt, TimeBlockStartsAt).
>Thanks, Tom. I've tried this (with a clustered index), but it is too
>slow for the amount of records I have. Not sure whether SQL can come up
>with something faster. I think I am gonna have to come with a
>completely alternative solution for this problem.
Is that what you want, a list out of your zillion rows of which have
overlaps, or will you have a fixed interval you want to search for?
I've done a little of this (more second case), and it is likely to be
slow (esp the first case), even when you're all indexed.
Might do a little better if the first test is t1.timeblockstartsat is
between x and y, if your intervals are typically short you can compute
reasonable x and y as start-i and start+i.
J.|||JXStern wrote:
> On Mon, 28 Aug 2006 14:53:29 -0700, Frank Rizzo <none@.none.com> wrote:
>> select
>> *
>> from
>> TimeBlocks t1
>> join
>> TimeBlocks t2 on (t1.TimeBlockStartsAt <= t2.TimeBlockStartsAt
>> and t1.TimeBlockEndsAt >= t2.TimeBlockStartsAt)
>> Also, create a clustered index on (TimeBlockStartsAt, TimeBlockStartsAt).
>> Thanks, Tom. I've tried this (with a clustered index), but it is too
>> slow for the amount of records I have. Not sure whether SQL can come up
>> with something faster. I think I am gonna have to come with a
>> completely alternative solution for this problem.
> Is that what you want, a list out of your zillion rows of which have
> overlaps, or will you have a fixed interval you want to search for?
> I've done a little of this (more second case), and it is likely to be
> slow (esp the first case), even when you're all indexed.
> Might do a little better if the first test is t1.timeblockstartsat is
> between x and y, if your intervals are typically short you can compute
> reasonable x and y as start-i and start+i.
Thanks, that's what I found. I think I'll have to change gears and
record more information when I enter the row (e.g. check whether it
overlaps then) and mark a field or something to that effect.

Friday, March 9, 2012

Faster select count(*)

About 50% of the search time is spent just counting the number of rows in
the resultset.
We page the resultset and show only one page at a time. Is there any way we
can count
the number of rows in the resultset faster?
CasperMore information, please -- counting what result set? Are you doing this on
the client side? Can you show the code you're currently using?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Casper Hornstrup" <msdn@.csite.com> wrote in message
news:OcflLTjLFHA.3000@.TK2MSFTNGP10.phx.gbl...
> About 50% of the search time is spent just counting the number of rows in
> the resultset.
> We page the resultset and show only one page at a time. Is there any way
we
> can count
> the number of rows in the resultset faster?
> Casper
>|||select rowcnt from sysindexes
where id = object_id(N'[table]') and indid = 1
(indid = 1) is PK constraints I believe, so may not work for heaps; other
indexes don't appear to track rowcnt. Also the rwcnt field I don't think is
kept 100% current, so probably not accurate enough for your needs. But it's
there anyways ;)
- KH
"Casper Hornstrup" wrote:

> About 50% of the search time is spent just counting the number of rows in
> the resultset.
> We page the resultset and show only one page at a time. Is there any way w
e
> can count
> the number of rows in the resultset faster?
> Casper
>
>|||http://www.aspfaq.com/2120
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Casper Hornstrup" <msdn@.csite.com> wrote in message
news:OcflLTjLFHA.3000@.TK2MSFTNGP10.phx.gbl...
> About 50% of the search time is spent just counting the number of rows in
> the resultset.
> We page the resultset and show only one page at a time. Is there any way
we
> can count
> the number of rows in the resultset faster?
> Casper
>|||> select rowcnt from sysindexes
> where id = object_id(N'[table]') and indid = 1
That will give you a rough count of what's in the entire table. However,
the OP said they wanted the count of the resultset... which is likely a
combination of a join between multiple tables and a filtered where clause.|||Yea, the original question is pretty ambiguious.
In the scenario you suggest using @.@.ROWCOUNT after the select might work for
this application.
It all depends eh? ;)
- KH
"Aaron [SQL Server MVP]" wrote:

> That will give you a rough count of what's in the entire table. However,
> the OP said they wanted the count of the resultset... which is likely a
> combination of a join between multiple tables and a filtered where clause.
>
>|||"examnotes" <KH@.discussions.microsoft.com> wrote in message news:<5EC86F57-C7F7-4AC0
-A197-B1C677605F8A@.microsoft.com>...
> Yea, the original question is pretty ambiguious.
> In the scenario you suggest using @.@.ROWCOUNT after the select might work f
or
> this application.
> It all depends eh? ;)
> - KH
>
> "Aaron [SQL Server MVP]" wrote:
>
respond

Wednesday, March 7, 2012

faster count(*) or alternative

hi,

I have a table with several million rows.
Each row is simply the date and time a certain page was viewed.

eg
page1 1-1-00
page2 2-1-00
page1 16-1-00
page1 17-1-00
page2 19-1-00

I now need to find the most popular pages so I do this...

SELECT TOP 10 COUNT(*) AS mycount FROM tblPageViews
GROUP BY place ORDER BY COUNT(place) DESC

...which gives me the top 10 most viewed pages

big problem - this is getting slower and slower as the table grows and
grows.

what should I do?

is there an alternative?

I think I need all the rows (rather than a simple incrementing
counter) because I might want to say "what was the most popular page
on a certain day or within a certain period"

tia

Timnot entirely sure why my browser posted my original message again!
sorry.

thanks guys for your help so far.
yes I have indexes on the columns

erland - I don't quite get what you are doing with creating and
dropping a table and a view?

Tim|||C10B (tswalton@.gmail.com) writes:

Quote:

Originally Posted by

erland - I don't quite get what you are doing with creating and
dropping a table and a view?

The DROP at the end is just cleanup. The script was meant to show how
you create an indexed view and how to use it.

--
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|||I'm still mildly puzzled as to how an indexed view would be quicker to
function than going straight to the table. Surely there's exactly the
same amount of work to be done?
I'll check it out though and let you know if it's any quicker.

Thanks

Tim|||"C10B" <tswalton@.gmail.comwrote in message
news:1172494748.828877.126110@.m58g2000cwm.googlegr oups.com...

Quote:

Originally Posted by

I'm still mildly puzzled as to how an indexed view would be quicker to
function than going straight to the table. Surely there's exactly the
same amount of work to be done?
I'll check it out though and let you know if it's any quicker.
>
Thanks
>
Tim
>

If you want more help, I suggest you give us a complete DDL of your
database, some real example data and perhaps we can do better.

--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||

Quote:

Originally Posted by

If you want more help, I suggest you give us a complete DDL of your
database, some real example data and perhaps we can do better.

The requirement is simply this...

I have a lot of pages and I want to record when each is visited.
I then need to be able to answer questions like this...

"Which was the most popular page in January 07?"
"Which are the top 10 most viewed pages between 2 and 3 in the
afternoon"

with unlimited segmenting of the data really.

So I thought I would record an entry in a table everytime a page is
viewed, along with the date and time.
Using some simple queries using "count(*)", a where clause and a group
by clause I was answering all the questions.
Trouble is the speed. Several million records (and growing rapidly)
and count(*) gets slow.

Just while I was writing this I had an idea - I could move a finished
month's data to another table, so each month has a table of data. It
might still be a million records though, but at least it wont get
worse and worse over time. This would make some results harder to get
like "most viewed page of all time" but my monthy, hourly, weekly
figures would be quicker.

Any other tips?

Thanks|||C10B (tswalton@.gmail.com) writes:

Quote:

Originally Posted by

I'm still mildly puzzled as to how an indexed view would be quicker to
function than going straight to the table. Surely there's exactly the
same amount of work to be done?

No. An indexed view is materialised, and this particular view has one
row per webpage with the count of visitors. An alternative would be have
a separate table with the counts, and update that table through a trigger.
But why roll our own when SQL Server can do the job for us?

--
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|||"C10B" <tswalton@.gmail.comwrote in message
news:1172502298.291068.60420@.s48g2000cws.googlegro ups.com...

Quote:

Originally Posted by

Quote:

Originally Posted by

>If you want more help, I suggest you give us a complete DDL of your
>database, some real example data and perhaps we can do better.

>
>
The requirement is simply this...
>
I have a lot of pages and I want to record when each is visited.
I then need to be able to answer questions like this...
>

Yes, we understand the requirements. That doesn't eliminate the value of a
DDL and sample data.

Quote:

Originally Posted by

"Which was the most popular page in January 07?"
"Which are the top 10 most viewed pages between 2 and 3 in the
afternoon"
>
with unlimited segmenting of the data really.
>
So I thought I would record an entry in a table everytime a page is
viewed, along with the date and time.
Using some simple queries using "count(*)", a where clause and a group
by clause I was answering all the questions.
Trouble is the speed. Several million records (and growing rapidly)
and count(*) gets slow.
>

Define slow?

(my former employer did queries similar to this in subsecond times.)

Quote:

Originally Posted by

Yes, post a full DDL.

Quote:

Originally Posted by

>
Thanks
>

--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||C10B (tswalton@.gmail.com) writes:

Quote:

Originally Posted by

I have a lot of pages and I want to record when each is visited.
I then need to be able to answer questions like this...
>
"Which was the most popular page in January 07?"
"Which are the top 10 most viewed pages between 2 and 3 in the
afternoon"
>
with unlimited segmenting of the data really.
>
So I thought I would record an entry in a table everytime a page is
viewed, along with the date and time.
Using some simple queries using "count(*)", a where clause and a group
by clause I was answering all the questions.
Trouble is the speed. Several million records (and growing rapidly)
and count(*) gets slow.

You did not say anything about wanting to get the counts for a certain
day, so I interpreted your question that you wanted total counts of
the entire table. Whence my suggestion of an indexed view.

If you are not able to relate clearly what you want to do, then the
answers you get will be equally imprecise.

Quote:

Originally Posted by

There is no need for that. If you always want to run these count
queries for a certain period, make sure that there is a clustered
index on the datetime column in your table.

faster count(*) or alternative

hi,

I have a table with several million rows.
Each row is simply the date and time a certain page was viewed.

eg
page1 1-1-00
page2 2-1-00
page1 16-1-00
page1 17-1-00
page2 19-1-00

I now need to find the most popular pages so I do this...

SELECT TOP 10 COUNT(*) AS mycount FROM tblPageViews
GROUP BY place ORDER BY COUNT(place) DESC

...which gives me the top 10 most viewed pages

big problem - this is getting slower and slower as the table grows and
grows.

what should I do?

is there an alternative?

I think I need all the rows (rather than a simple incrementing
counter) because I might want to say "what was the most popular page
on a certain day or within a certain period"

tia

Tim"C10B" <tswalton@.gmail.comwrote in message
news:1172439270.641426.83160@.a75g2000cwd.googlegro ups.com...

Quote:

Originally Posted by

hi,
>
I have a table with several million rows.
Each row is simply the date and time a certain page was viewed.

First question, do you have an index on the table.

That should help.

Quote:

Originally Posted by

>
eg
page1 1-1-00
page2 2-1-00
page1 16-1-00
page1 17-1-00
page2 19-1-00
>
I now need to find the most popular pages so I do this...
>
SELECT TOP 10 COUNT(*) AS mycount FROM tblPageViews
GROUP BY place ORDER BY COUNT(place) DESC
>
...which gives me the top 10 most viewed pages
>
big problem - this is getting slower and slower as the table grows and
grows.
>
what should I do?
>
is there an alternative?
>
I think I need all the rows (rather than a simple incrementing
counter) because I might want to say "what was the most popular page
on a certain day or within a certain period"
>
tia
>
Tim
>

--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||C10B (tswalton@.gmail.com) writes:

Quote:

Originally Posted by

I have a table with several million rows.
Each row is simply the date and time a certain page was viewed.
>
eg
page1 1-1-00
page2 2-1-00
page1 16-1-00
page1 17-1-00
page2 19-1-00
>
I now need to find the most popular pages so I do this...
>
SELECT TOP 10 COUNT(*) AS mycount FROM tblPageViews
GROUP BY place ORDER BY COUNT(place) DESC
>
...which gives me the top 10 most viewed pages
>
big problem - this is getting slower and slower as the table grows and
grows.
>
what should I do?

A non-clustered index on place would help, as SQL Server then only
would have to scan that index, and not the entire difference. But it
seems that the table has two columns. In such case, the index would not
reduce execution time that much.

A better alternative may be to define an indexed view that maintains
the count:

CREATE TABLE pagehits (pageid varchar(20) NOT NULL,
viewtime datetime NOT NULL,
PRIMARY KEY (pageid, viewtime)
)
go
CREATE VIEW pagecount WITH SCHEMABINDING AS
SELECT pageid, cnt = COUNT_BIG(*)
FROM dbo.pagehits
GROUP BY pageid
go
CREATE UNIQUE CLUSTERED INDEX pagecount_ix ON pagecount(pageid)
go
SELECT TOP 10 pageid, cnt
FROM pagecount WITH (NOEXPAND)
ORDER BY cnt
go
DROP VIEW pagecount
DROP TABLE pagehits

I added the NOEXPAND hint to the query, since it's only on Enterprise
Edition, the optimizer considers indexed views.

Note that this could have effect on performance when writing to the table.

--
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|||Fastest returning query would be on a separate table that maintains the
count to the granularity you would require in your output (week, day, or
hour). A query that requires more than hourly granularity can still be done
on the source table.

For each new week|day|hour add a row for each page to be tracked. This can
be done dynamically during monitoring but it is better to do it ahead of
time.

Then put an insert trigger on your million row table that will increment the
count on the proper row of the table.

An alternative would be a temp table that contains a subset copy of the
source data for the time range most likely to queried. For example a table
that contains the last 31 days of data, the 32nd day of data
deleted/archived every night.

"C10B" <tswalton@.gmail.comwrote in message
news:1172439270.641426.83160@.a75g2000cwd.googlegro ups.com...

Quote:

Originally Posted by

hi,
>
I have a table with several million rows.
Each row is simply the date and time a certain page was viewed.
>
eg
page1 1-1-00
page2 2-1-00
page1 16-1-00
page1 17-1-00
page2 19-1-00
>
I now need to find the most popular pages so I do this...
>
SELECT TOP 10 COUNT(*) AS mycount FROM tblPageViews
GROUP BY place ORDER BY COUNT(place) DESC
>
...which gives me the top 10 most viewed pages
>
big problem - this is getting slower and slower as the table grows and
grows.
>
what should I do?
>
is there an alternative?
>
I think I need all the rows (rather than a simple incrementing
counter) because I might want to say "what was the most popular page
on a certain day or within a certain period"
>
tia
>
Tim
>

|||erland - you are quite right, I could have worded my first post a
little better

greg - I don't know what a DDL is

russ - thanks|||C10B (tswalton@.gmail.com) writes:

Quote:

Originally Posted by

greg - I don't know what a DDL is

DDL = Data Definition Language. That is, CREATE TABLE and CREATE INDEX and
the like.

If I had had a penny for every time someone says DDL in answer, without the
person asking having no idea what it means, I would be a rich man now.

Quote:

Originally Posted by

russ - thanks

What Russ proposed is the same idea that I proposed, but rather than relying
SQL Server updating an indexed view, he suggested a separate table that you
update through a trigger.

--
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|||"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns98E480B95D736Yazorman@.127.0.0.1...

Quote:

Originally Posted by

C10B (tswalton@.gmail.com) writes:

Quote:

Originally Posted by

>greg - I don't know what a DDL is

>
DDL = Data Definition Language. That is, CREATE TABLE and CREATE INDEX and
the like.
>
If I had had a penny for every time someone says DDL in answer, without
the
person asking having no idea what it means, I would be a rich man now.
>

Quote:

Originally Posted by

>russ - thanks

>
What Russ proposed is the same idea that I proposed, but rather than
relying
SQL Server updating an indexed view, he suggested a separate table that
you
update through a trigger.
>

Indexed view is probably best, I just like having more control.

Quote:

Originally Posted by

>
--
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

Fast insert and select at the same time?

I have two tables: Account and AccountTransction. Each table contains more
than 20 million records. It one-to-many relationship between account and
AccountTransaction. The new records are constantly loading into each table
through text file by using DTS.
Question:
My team member insists that using cursor to insert record one by one to the
table to avoid affect (lock) the selection on these tables. . There are tons
of indexes on both tables for fast searching. The insertion process is
extremely slow. I recommended batch mode insertion, instead of one by one
using cursor. It is much more faster and efficient in terms of insertion,
but the selection while insertion going on is a little bit slower. What is
your suggestion? How can I achieve the fast insertion and fast selection at
the same time'
Is cursor alway a bad idea in terms of speed and performance?
Thanks a lot,
FlxI would never use a cursor to insert one row of data one by one...
Your colleague is smart to have worries about contention. However... it's
quite easy to do this in a safe manner. My standard technique for manaing a
situation like this is to:
* insert N number of rows per batch through an insert into stmt
* N is tested to ensure
- the insert happens fast enough to have a negligible impact on blocks
for selects
- durtion between batch inserts is long enough to ensure we're not
having a constant impact and quueses aren't growing
- but N is large enough to ensure I can insert enough records fast
enough such that the insert process isn't horrible slow.
I've been able to achieve VERY high insert and select throughput using
techniques like that...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"FLX" <nospam@.hotmail.com> wrote in message
news:e5rEoTiAEHA.3004@.TK2MSFTNGP10.phx.gbl...
> I have two tables: Account and AccountTransction. Each table contains more
> than 20 million records. It one-to-many relationship between account and
> AccountTransaction. The new records are constantly loading into each table
> through text file by using DTS.
> Question:
> My team member insists that using cursor to insert record one by one to
the
> table to avoid affect (lock) the selection on these tables. . There are
tons
> of indexes on both tables for fast searching. The insertion process is
> extremely slow. I recommended batch mode insertion, instead of one by one
> using cursor. It is much more faster and efficient in terms of insertion,
> but the selection while insertion going on is a little bit slower. What is
> your suggestion? How can I achieve the fast insertion and fast selection
at
> the same time'
> Is cursor alway a bad idea in terms of speed and performance?
> Thanks a lot,
> Flx
>
>
>
>
>
>
>
>
>
>
>|||Here's an out-of-the-box idea.
Create new tables, same schema, so you have pairs to tables.
These new tables are for 'todays' data.
Create views to cover the pairs of tables. These are what your application/users look at / use.
The DTS populates the 'today' tables.
Once a day, at some light / quite period, stop the DTS. Copy / Move 'todays' data into the oringal, large table
Don't index the 'todays' tables, as they will (hopefully) be small enough to not need them. Or add only essential indexes.
Or only do this for the AccountTransaction table, and use the current method for the Account table.
If possible, you might want to drop the main table indexes just before you load the data from the 'todays' tables.
It depends, of course, on how quite your quite period will be.

Fast insert and select at the same time?

I have two tables: Account and AccountTransction. Each table contains more
than 20 million records. It one-to-many relationship between account and
AccountTransaction. The new records are constantly loading into each table
through text file by using DTS.
Question:
My team member insists that using cursor to insert record one by one to the
table to avoid affect (lock) the selection on these tables. . There are tons
of indexes on both tables for fast searching. The insertion process is
extremely slow. I recommended batch mode insertion, instead of one by one
using cursor. It is much more faster and efficient in terms of insertion,
but the selection while insertion going on is a little bit slower. What is
your suggestion? How can I achieve the fast insertion and fast selection at
the same time'
Is cursor alway a bad idea in terms of speed and performance?
Thanks a lot,
FlxI would never use a cursor to insert one row of data one by one...
Your colleague is smart to have worries about contention. However... it's
quite easy to do this in a safe manner. My standard technique for manaing a
situation like this is to:
* insert N number of rows per batch through an insert into stmt
* N is tested to ensure
- the insert happens fast enough to have a negligible impact on blocks
for selects
- durtion between batch inserts is long enough to ensure we're not
having a constant impact and quueses aren't growing
- but N is large enough to ensure I can insert enough records fast
enough such that the insert process isn't horrible slow.
I've been able to achieve VERY high insert and select throughput using
techniques like that...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"FLX" <nospam@.hotmail.com> wrote in message
news:e5rEoTiAEHA.3004@.TK2MSFTNGP10.phx.gbl...
> I have two tables: Account and AccountTransction. Each table contains more
> than 20 million records. It one-to-many relationship between account and
> AccountTransaction. The new records are constantly loading into each table
> through text file by using DTS.
> Question:
> My team member insists that using cursor to insert record one by one to
the
> table to avoid affect (lock) the selection on these tables. . There are
tons
> of indexes on both tables for fast searching. The insertion process is
> extremely slow. I recommended batch mode insertion, instead of one by one
> using cursor. It is much more faster and efficient in terms of insertion,
> but the selection while insertion going on is a little bit slower. What is
> your suggestion? How can I achieve the fast insertion and fast selection
at
> the same time'
> Is cursor alway a bad idea in terms of speed and performance?
> Thanks a lot,
> Flx
>
>
>
>
>
>
>
>
>
>
>|||Here's an out-of-the-box idea.
Create new tables, same schema, so you have pairs to tables.
These new tables are for 'todays' data.
Create views to cover the pairs of tables. These are what your application/u
sers look at / use.
The DTS populates the 'today' tables.
Once a day, at some light / quite period, stop the DTS. Copy / Move 'todays'
data into the oringal, large table
Don't index the 'todays' tables, as they will (hopefully) be small enough to
not need them. Or add only essential indexes.
Or only do this for the AccountTransaction table, and use the current method
for the Account table.
If possible, you might want to drop the main table indexes just before you l
oad the data from the 'todays' tables.
It depends, of course, on how quite your quite period will be.

Sunday, February 26, 2012

Fast "bulk" inserts...

I have an app that must insert batches of transaction records every 30 min.
Typically only a few hundred each time. We found that just appending a
hundred or so INSERT commands together in a single query sent to the server
was significantly faster than our first approach of preparing a paraemterize
d
query and executing once for each record. So now we send only a few commands
to SQL instead of hundreds.
Someone suggested to me that we put all of the records into an XML
"document" and use OPENXML. We will test this approach.
Any suggestions, guesses on how the new approach and other possible
approaches to try?
Also, would a different approach be warranted if the number of records in a
batch was around 10K, instead of just 200-300?
Thanks,
LMcPhee"lmcphee" <lmcphee@.discussions.microsoft.com> wrote in message
news:E2D39232-D7B3-450C-B76F-5536DC10FEF5@.microsoft.com...
>I have an app that must insert batches of transaction records every 30 min.
> Typically only a few hundred each time. We found that just appending a
> hundred or so INSERT commands together in a single query sent to the
> server
> was significantly faster than our first approach of preparing a
> paraemterized
> query and executing once for each record. So now we send only a few
> commands
> to SQL instead of hundreds.
> Someone suggested to me that we put all of the records into an XML
> "document" and use OPENXML. We will test this approach.
If you can get by with your current approach, do. In SQL 2005/ADO.NET 2.0
you will have the SQLBulkInsert, which will be the perfered method.

> Any suggestions, guesses on how the new approach and other possible
> approaches to try?
> Also, would a different approach be warranted if the number of records in
> a
> batch was around 10K, instead of just 200-300?
>
The batching approach will still work, but you wouldn't want to batch 10k
rows. That's just too much SQL. Keep your batch size constant, and send
multiple batches if necessary.
Another critically important thing for running multiple inserts is to use an
explicit transaction. Without an explicit transaction SQL Server must flush
the log to disk (aka commit) after every insert. The commit takes much
longer than just doing the insert, so you want to do all the inserts, and
just force a single commit at the end.
David

Monday, March 26, 2012

Fetch limited rows sequentially

Wednesday, March 21, 2012

Fatal Exception in SQL Server

Fatal Exception in SQL Server

Fatal Exception in SQL Server

Monday, March 12, 2012

Fastest way to query Oracle

Fastest way to query Oracle

Fastest way to query Oracle

Fastest way to locate overlapping time blocks

Fastest way to locate overlapping time blocks

Friday, March 9, 2012

Faster select count(*)

Wednesday, March 7, 2012

faster count(*) or alternative

faster count(*) or alternative

Fast insert and select at the same time?

Fast insert and select at the same time?

Sunday, February 26, 2012

Fast "bulk" inserts...

Footer rows to skip

Blog Archive

About Me