I have two rows in table1 and these two rows are then duplicated in
table2 making four rows. I then try and use the following query to
select the four rows from table2.
select * from Product P
where P.ProductVersionID in (select ProductVersionID from PartSet where
SetID = ?)
Running the above query only return the two rows from Product instead
of the four rows that exist in SetPart.
Now if I run the query below I get back the four rows that I want.
select * from Product P
inner join SetPart SP on P.ProductVersionID = SP.ProductVersionID where
SP.SetID = ?
Can someone please explain to me why the query with the IN clause
doesn't return the four rows that I want?
DML:
CREATE TABLE SetPart (
SetID int NOT NULL,
ProductVersionID int NOT NULL,
ProductTypeID int NOT NULL
)
GO
CREATE TABLE Product (
ProductVersionID int NOT NULL,
ProductName varchar (20) NOT NULL
)
GOmohaaron@.gmail.com wrote:
> I have two rows in table1 and these two rows are then duplicated in
> table2 making four rows. I then try and use the following query to
> select the four rows from table2.
> select * from Product P
> where P.ProductVersionID in (select ProductVersionID from PartSet where
> SetID = ?)
> Running the above query only return the two rows from Product instead
> of the four rows that exist in SetPart.
> Now if I run the query below I get back the four rows that I want.
> select * from Product P
> inner join SetPart SP on P.ProductVersionID = SP.ProductVersionID where
> SP.SetID = ?
> Can someone please explain to me why the query with the IN clause
> doesn't return the four rows that I want?
Because IN is not a JOIN. IN just determines whether a row (or rows)
exists in the subquery. Apparently you want the join instead.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>> Can someone please explain to me why the query with the IN clause doesn't
IN() implies only the distinct rows in a subquery result. It is different
from an INNER JOIN which returns all rows based on the column used in the
join. If the columns participating in the INNER JOIN are not unique
duplicates can occur in the result.
In general, keyless tables are practically useless and it is recommended
that every table must have a column or set of columns that can uniquely
identify a row in the table.
Anith
Showing posts with label rows. Show all posts
Showing posts with label rows. Show all posts
Monday, March 26, 2012
Fetching Duplicate Rows
Hi,
I have some duplicate rows in a table. I didnt define any primary key or unique key on the table.
I can get unique rows using DISTINCT, but i want to fetch only the duplicated rows and also i want to delete the duplicated rows.
How can i do it?
Please help me....
Thanx in AdvanceI have some duplicate rows in a table. I didnt define any primary key or unique key on the table.
Can you paste some DDL. Are all the columns for the duplicated rows same ? Is there any date time stamp ? You could find some help Here (http://www.sqlteam.com/item.asp?ItemID=3331)|||Try something like:
Select * from Table
Where {Key Fields} IN
(select {key Fields} from Table
group by {Key Fields}
Having Count(*) >1)
Where Table is your Table/Query and {Key Fields} is the list of fields you want to search for duplicates on
HTH
Marp
[
QUOTE]Originally posted by d_kishan
Hi,
I have some duplicate rows in a table. I didnt define any primary key or unique key on the table.
I can get unique rows using DISTINCT, but i want to fetch only the duplicated rows and also i want to delete the duplicated rows.
How can i do it?
Please help me....
Thanx in Advance [/QUOTE]|||You say you want to delete the duplicated rows, but I bet what you really want is to delete all but one of each duplicated row. Your first step is to add a primary key. Otherwise, there is no way to discern which of the duplicated rows to retain and your only option will be to select DISTINCT into a new table and then replace your old table.|||If you want to know how many duplicate rows you have per your Key criteria,
do this
[Select Key1,Key2,..Keyn, count(*)
from Table
group by {Key1,Key2,...Keyn}
Having Count(*) >1)
I have some duplicate rows in a table. I didnt define any primary key or unique key on the table.
I can get unique rows using DISTINCT, but i want to fetch only the duplicated rows and also i want to delete the duplicated rows.
How can i do it?
Please help me....
Thanx in AdvanceI have some duplicate rows in a table. I didnt define any primary key or unique key on the table.
Can you paste some DDL. Are all the columns for the duplicated rows same ? Is there any date time stamp ? You could find some help Here (http://www.sqlteam.com/item.asp?ItemID=3331)|||Try something like:
Select * from Table
Where {Key Fields} IN
(select {key Fields} from Table
group by {Key Fields}
Having Count(*) >1)
Where Table is your Table/Query and {Key Fields} is the list of fields you want to search for duplicates on
HTH
Marp
[
QUOTE]Originally posted by d_kishan
Hi,
I have some duplicate rows in a table. I didnt define any primary key or unique key on the table.
I can get unique rows using DISTINCT, but i want to fetch only the duplicated rows and also i want to delete the duplicated rows.
How can i do it?
Please help me....
Thanx in Advance [/QUOTE]|||You say you want to delete the duplicated rows, but I bet what you really want is to delete all but one of each duplicated row. Your first step is to add a primary key. Otherwise, there is no way to discern which of the duplicated rows to retain and your only option will be to select DISTINCT into a new table and then replace your old table.|||If you want to know how many duplicate rows you have per your Key criteria,
do this
[Select Key1,Key2,..Keyn, count(*)
from Table
group by {Key1,Key2,...Keyn}
Having Count(*) >1)
Fetch rows afftected in a table
Hi,
I'm pretty new to this community and here's my query. Lets suppose I have executed a DML query on a table (inserted a single row). The table is not having any identity, date or time fields....just a primary key column. I wish to retrieve the record that I or anybody else inserted last into that table. Or I might even wish to fetch all records inserted into that table in the last 10 seconds. Can anybody here help me out in this case, plz.
Yah.
I got the same doubt .is there any to find the time of insertion of a particular row|||
You could add columns UpdatedByID and LastUpdateDate and set UpdatedByID to be the user's username and set LastUpdateDate to GETDATE() (or just make the default for LastUpdateDate to be GETDATE()).
I'm pretty new to this community and here's my query. Lets suppose I have executed a DML query on a table (inserted a single row). The table is not having any identity, date or time fields....just a primary key column. I wish to retrieve the record that I or anybody else inserted last into that table. Or I might even wish to fetch all records inserted into that table in the last 10 seconds. Can anybody here help me out in this case, plz.
Quote:
Originally Posted by dev177
Hi,
I'm pretty new to this community and here's my query. Lets suppose I have executed a DML query on a table (inserted a single row). The table is not having any identity, date or time fields....just a primary key column. I wish to retrieve the record that I or anybody else inserted last into that table. Or I might even wish to fetch all records inserted into that table in the last 10 seconds. Can anybody here help me out in this case, plz.
Yah.
I got the same doubt .is there any to find the time of insertion of a particular row|||
Quote:
Originally Posted by srinit
Yah.
I got the same doubt .is there any to find the time of insertion of a particular row
You could add columns UpdatedByID and LastUpdateDate and set UpdatedByID to be the user's username and set LastUpdateDate to GETDATE() (or just make the default for LastUpdateDate to be GETDATE()).
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
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
Monday, March 12, 2012
fastest way to recieve results?
Hi,
I want to call a stored proc. on SQL 2000 from VC 6.0. This stored proc
can return 1-4 rows. After extensive reading and overwhelming myself with
help files from MSDN 6.0, MDAC 2.8, SQL 2000 and hte platform SDK, I want
to optimize my software performance. I want to do only one round trip to
the server which will include a request to the server and its response
should be all 4 rows of data as a block.
some help files say for read-only-Fast-forward cursors wit ha small result
set, client mode with auto-fetch option is the best way. other help files
say odbc client mode library cursors are slower and way more memory
intensive than native SQL server libraries so caution should be used. what
should i do?
currently i use:
SQLSetConnectAttr(*hdbc, SQL_ATTR_ODBC_CURSORS, (SQLPOINTER)
SQL_CUR_USE_ODBC,0); ////odbc test
SQLDriverConnect(...)
SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_BIND_TYPE, (PTR)sizeof(SQLARRAY), 0);
SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (PTR) 4, 0);
SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_STATUS_PTR, (SQLPOINTER)RowStatusArray,
0);
SQLSetStmtAttr(hstmt, SQL_ATTR_ROWS_FETCHED_PTR, &NumRowsFetched, 0);
is this optimized?
LeonLeon McCalla wrote:
> Hi,
> I want to call a stored proc. on SQL 2000 from VC 6.0. This stored proc
> can return 1-4 rows. After extensive reading and overwhelming myself with
> help files from MSDN 6.0, MDAC 2.8, SQL 2000 and hte platform SDK, I want
> to optimize my software performance. I want to do only one round trip to
> the server which will include a request to the server and its response
> should be all 4 rows of data as a block.
Why? What do you have that is so time critical?
Aaron|||I'm working on a calling card application. millions of callingcards are
already on the street and sometimes i get 10-20 requests per second.
PS i already solver the problem.
Leon
"Aaron Lawrence" <aaronlNOT@.HEREintegration.co.nz> wrote in message
news:eu5N0MZ$GHA.3836@.TK2MSFTNGP02.phx.gbl...
> Leon McCalla wrote:
proc[vbcol=seagreen]
with[vbcol=seagreen]
want[vbcol=seagreen]
to[vbcol=seagreen]
> Why? What do you have that is so time critical?
> Aaron
>
I want to call a stored proc. on SQL 2000 from VC 6.0. This stored proc
can return 1-4 rows. After extensive reading and overwhelming myself with
help files from MSDN 6.0, MDAC 2.8, SQL 2000 and hte platform SDK, I want
to optimize my software performance. I want to do only one round trip to
the server which will include a request to the server and its response
should be all 4 rows of data as a block.
some help files say for read-only-Fast-forward cursors wit ha small result
set, client mode with auto-fetch option is the best way. other help files
say odbc client mode library cursors are slower and way more memory
intensive than native SQL server libraries so caution should be used. what
should i do?
currently i use:
SQLSetConnectAttr(*hdbc, SQL_ATTR_ODBC_CURSORS, (SQLPOINTER)
SQL_CUR_USE_ODBC,0); ////odbc test
SQLDriverConnect(...)
SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_BIND_TYPE, (PTR)sizeof(SQLARRAY), 0);
SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (PTR) 4, 0);
SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_STATUS_PTR, (SQLPOINTER)RowStatusArray,
0);
SQLSetStmtAttr(hstmt, SQL_ATTR_ROWS_FETCHED_PTR, &NumRowsFetched, 0);
is this optimized?
LeonLeon McCalla wrote:
> Hi,
> I want to call a stored proc. on SQL 2000 from VC 6.0. This stored proc
> can return 1-4 rows. After extensive reading and overwhelming myself with
> help files from MSDN 6.0, MDAC 2.8, SQL 2000 and hte platform SDK, I want
> to optimize my software performance. I want to do only one round trip to
> the server which will include a request to the server and its response
> should be all 4 rows of data as a block.
Why? What do you have that is so time critical?
Aaron|||I'm working on a calling card application. millions of callingcards are
already on the street and sometimes i get 10-20 requests per second.
PS i already solver the problem.
Leon
"Aaron Lawrence" <aaronlNOT@.HEREintegration.co.nz> wrote in message
news:eu5N0MZ$GHA.3836@.TK2MSFTNGP02.phx.gbl...
> Leon McCalla wrote:
proc[vbcol=seagreen]
with[vbcol=seagreen]
want[vbcol=seagreen]
to[vbcol=seagreen]
> Why? What do you have that is so time critical?
> Aaron
>
fastest way to recieve results?
Hi,
I want to call a stored proc. on SQL 2000 from VC 6.0. This stored proc
can return 1-4 rows. After extensive reading and overwhelming myself with
help files from MSDN 6.0, MDAC 2.8, SQL 2000 and hte platform SDK, I want
to optimize my software performance. I want to do only one round trip to
the server which will include a request to the server and its response
should be all 4 rows of data as a block.
some help files say for read-only-Fast-forward cursors wit ha small result
set, client mode with auto-fetch option is the best way. other help files
say odbc client mode library cursors are slower and way more memory
intensive than native SQL server libraries so caution should be used. what
should i do?
currently i use:
SQLSetConnectAttr(*hdbc, SQL_ATTR_ODBC_CURSORS, (SQLPOINTER)
SQL_CUR_USE_ODBC,0); ////odbc test
SQLDriverConnect(...)
SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_BIND_TYPE, (PTR)sizeof(SQLARRAY), 0);
SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (PTR) 4, 0);
SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_STATUS_PTR, (SQLPOINTER)RowStatusArray,
0);
SQLSetStmtAttr(hstmt, SQL_ATTR_ROWS_FETCHED_PTR, &NumRowsFetched, 0);
is this optimized?
Leon
Leon McCalla wrote:
> Hi,
> I want to call a stored proc. on SQL 2000 from VC 6.0. This stored proc
> can return 1-4 rows. After extensive reading and overwhelming myself with
> help files from MSDN 6.0, MDAC 2.8, SQL 2000 and hte platform SDK, I want
> to optimize my software performance. I want to do only one round trip to
> the server which will include a request to the server and its response
> should be all 4 rows of data as a block.
Why? What do you have that is so time critical?
Aaron
|||I'm working on a calling card application. millions of callingcards are
already on the street and sometimes i get 10-20 requests per second.
PS i already solver the problem.
Leon
"Aaron Lawrence" <aaronlNOT@.HEREintegration.co.nz> wrote in message
news:eu5N0MZ$GHA.3836@.TK2MSFTNGP02.phx.gbl...[vbcol=seagreen]
> Leon McCalla wrote:
proc[vbcol=seagreen]
with[vbcol=seagreen]
want[vbcol=seagreen]
to
> Why? What do you have that is so time critical?
> Aaron
>
I want to call a stored proc. on SQL 2000 from VC 6.0. This stored proc
can return 1-4 rows. After extensive reading and overwhelming myself with
help files from MSDN 6.0, MDAC 2.8, SQL 2000 and hte platform SDK, I want
to optimize my software performance. I want to do only one round trip to
the server which will include a request to the server and its response
should be all 4 rows of data as a block.
some help files say for read-only-Fast-forward cursors wit ha small result
set, client mode with auto-fetch option is the best way. other help files
say odbc client mode library cursors are slower and way more memory
intensive than native SQL server libraries so caution should be used. what
should i do?
currently i use:
SQLSetConnectAttr(*hdbc, SQL_ATTR_ODBC_CURSORS, (SQLPOINTER)
SQL_CUR_USE_ODBC,0); ////odbc test
SQLDriverConnect(...)
SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_BIND_TYPE, (PTR)sizeof(SQLARRAY), 0);
SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (PTR) 4, 0);
SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_STATUS_PTR, (SQLPOINTER)RowStatusArray,
0);
SQLSetStmtAttr(hstmt, SQL_ATTR_ROWS_FETCHED_PTR, &NumRowsFetched, 0);
is this optimized?
Leon
Leon McCalla wrote:
> Hi,
> I want to call a stored proc. on SQL 2000 from VC 6.0. This stored proc
> can return 1-4 rows. After extensive reading and overwhelming myself with
> help files from MSDN 6.0, MDAC 2.8, SQL 2000 and hte platform SDK, I want
> to optimize my software performance. I want to do only one round trip to
> the server which will include a request to the server and its response
> should be all 4 rows of data as a block.
Why? What do you have that is so time critical?
Aaron
|||I'm working on a calling card application. millions of callingcards are
already on the street and sometimes i get 10-20 requests per second.
PS i already solver the problem.
Leon
"Aaron Lawrence" <aaronlNOT@.HEREintegration.co.nz> wrote in message
news:eu5N0MZ$GHA.3836@.TK2MSFTNGP02.phx.gbl...[vbcol=seagreen]
> Leon McCalla wrote:
proc[vbcol=seagreen]
with[vbcol=seagreen]
want[vbcol=seagreen]
to
> Why? What do you have that is so time critical?
> Aaron
>
Fastest way to handle the search
Hi!
We have Sql Server 2000 in our server (NT 4). Our database have now about
+350.000 rows with information of images. Table have lot of columns
including information about image name, keywords, location, price, color
mode etc. So our database don?t include the images itself, just a path to
the location of every image. Keywords -field have data for example like
this:
cat,animal,pet,home,child with pet,child. Now our search use Full-Text
Search which sounded like good idea in the beginning but now it have had
problems that really reduce our search engine?s performance. Also search
results are not exact enough. Some of our images have also photographer?s
name in keywords -column and if photographer?s name is, for example, Peter
Moss, his pictures appears in web-page when customer want to search "moss"
(nature-like) -pictures.
Another problem is that Full-Text Search started to be very slow when query
result contains thousands of rows. When search term gives maximum 3000
rows, search is fast but larger searches take from 6 to 20 seconds to
finish which is not good. I have noticed also that first search is always
very slow, but next ones are faster. It seems that engine is just
"starting" when first query started to run.
Is there better and faster way to handle the queries? Is it better to
rebuild the database somehow and use another method to search than Full-
Text Search? I don?t know how to handle the database other way when every
image have about 10 to even 50 different keywords to search.
We have made web interface and search code with Coldfusion. Coldfusion
Server then take care of sending all queries to Sql Server.
I hope that somebody have some idea how to speed up our picture search.
We have Sql Server 2000 in our server (NT 4). Our database have now about
+350.000 rows with information of images. Table have lot of columns
including information about image name, keywords, location, price, color
mode etc. So our database don?t include the images itself, just a path to
the location of every image. Keywords -field have data for example like
this:
cat,animal,pet,home,child with pet,child. Now our search use Full-Text
Search which sounded like good idea in the beginning but now it have had
problems that really reduce our search engine?s performance. Also search
results are not exact enough. Some of our images have also photographer?s
name in keywords -column and if photographer?s name is, for example, Peter
Moss, his pictures appears in web-page when customer want to search "moss"
(nature-like) -pictures.
Another problem is that Full-Text Search started to be very slow when query
result contains thousands of rows. When search term gives maximum 3000
rows, search is fast but larger searches take from 6 to 20 seconds to
finish which is not good. I have noticed also that first search is always
very slow, but next ones are faster. It seems that engine is just
"starting" when first query started to run.
Is there better and faster way to handle the queries? Is it better to
rebuild the database somehow and use another method to search than Full-
Text Search? I don?t know how to handle the database other way when every
image have about 10 to even 50 different keywords to search.
We have made web interface and search code with Coldfusion. Coldfusion
Server then take care of sending all queries to Sql Server.
I hope that somebody have some idea how to speed up our picture search.
--
Message posted via http://www.sqlmonster.comOn Sat, 09 Apr 2005 08:30:43 GMT, Zambo via SQLMonster.com wrote:
(snip)
>search
>results are not exact enough. Some of our images have also photographer?s
>name in keywords -column and if photographer?s name is, for example, Peter
>Moss, his pictures appears in web-page when customer want to search "moss"
>(nature-like) -pictures.
Hi Zamobo,
That's easy to fix (easy from the designer's POV - it'll be a lot of
work for the data-entry person): add a column for the photographer's
name (set it to allow NULL if it's unknown for some of the images) and
move the name from the keywords column to the new column. Then change
the web form to accept seperate keywords and photographer name for the
search.
>Another problem is that Full-Text Search started to be very slow when query
>result contains thousands of rows. When search term gives maximum 3000
>rows, search is fast but larger searches take from 6 to 20 seconds to
>finish which is not good. I have noticed also that first search is always
>very slow, but next ones are faster. It seems that engine is just
>"starting" when first query started to run.
You might try if adding OPTION (FAST n) at the end of your query (where
n is replaced by some number) helps. This is an optimizer hint,
basically telling the optimzer to find a plan where the firs n rows are
returned as soon as possible, even if that means that the total query
execution might take longer. With appropriate handling at the front end,
you could use it to bring up the first page of results quickly, while
still gathering remaining results in the background. The slower
execution of the total query won't be a problem if the user is checking
the first page of results.
I must add that I'm not familiar with Full-Text Search, so I don't know
if OPTION (FAST n) really works in combination with FTS.
>Is there better and faster way to handle the queries? Is it better to
>rebuild the database somehow and use another method to search than Full-
>Text Search?
Based on your description, I think that FTS is the best way to handle
this. But you might consider posting your question in
microsoft.public.sqlserver.fulltext. As the name of that group implies,
it's dedicated to Full-Text Search issues.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks for the answer. Unfortunately it seems that OPTION(FAST n) is not
the answer in my case; query was much more slower when I asked the search
to return first 20 records faster. We have 20 pictures/page in our query
result. It seems that Coldfusion don?t return any records before the whole
query is over. I read from somewhere that FTS goes slow when there are
thousands of rows returned. But there must be a way to speed it up.
--
Message posted via http://www.sqlmonster.com
Fastest way to determine if a query returns any rows
Hi,
I hope I don't embarres myself because of the simplicity of this questrion,
but what would be the fastest way to determine, if a query would return any
rows (without actually returning them, and I don't need to know how many
there are, either)?
Thanks,
KlausYou can use the EXISTS() clause like:
IF EXISTS( SELECT * FROM ... )
Anith|||Thanks!
"Anith Sen" <anith@.bizdatasolutions.com> schrieb im Newsbeitrag
news:OOgsG%23LMFHA.3296@.TK2MSFTNGP15.phx.gbl...
> You can use the EXISTS() clause like:
> IF EXISTS( SELECT * FROM ... )
> --
> Anith
>
I hope I don't embarres myself because of the simplicity of this questrion,
but what would be the fastest way to determine, if a query would return any
rows (without actually returning them, and I don't need to know how many
there are, either)?
Thanks,
KlausYou can use the EXISTS() clause like:
IF EXISTS( SELECT * FROM ... )
Anith|||Thanks!
"Anith Sen" <anith@.bizdatasolutions.com> schrieb im Newsbeitrag
news:OOgsG%23LMFHA.3296@.TK2MSFTNGP15.phx.gbl...
> You can use the EXISTS() clause like:
> IF EXISTS( SELECT * FROM ... )
> --
> Anith
>
Fastest way to determine if a query returns any rows
Hi,
I hope I don't embarres myself because of the simplicity of this questrion,
but what would be the fastest way to determine, if a query would return any
rows (without actually returning them, and I don't need to know how many
there are, either)?
Thanks,
Klaus
You can use the EXISTS() clause like:
IF EXISTS( SELECT * FROM ... )
Anith
|||Thanks!
"Anith Sen" <anith@.bizdatasolutions.com> schrieb im Newsbeitrag
news:OOgsG%23LMFHA.3296@.TK2MSFTNGP15.phx.gbl...
> You can use the EXISTS() clause like:
> IF EXISTS( SELECT * FROM ... )
> --
> Anith
>
I hope I don't embarres myself because of the simplicity of this questrion,
but what would be the fastest way to determine, if a query would return any
rows (without actually returning them, and I don't need to know how many
there are, either)?
Thanks,
Klaus
You can use the EXISTS() clause like:
IF EXISTS( SELECT * FROM ... )
Anith
|||Thanks!
"Anith Sen" <anith@.bizdatasolutions.com> schrieb im Newsbeitrag
news:OOgsG%23LMFHA.3296@.TK2MSFTNGP15.phx.gbl...
> You can use the EXISTS() clause like:
> IF EXISTS( SELECT * FROM ... )
> --
> Anith
>
Fastest way to determine if a query returns any rows
Hi,
I hope I don't embarres myself because of the simplicity of this questrion,
but what would be the fastest way to determine, if a query would return any
rows (without actually returning them, and I don't need to know how many
there are, either)?
Thanks,
KlausYou can use the EXISTS() clause like:
IF EXISTS( SELECT * FROM ... )
--
Anith|||Thanks!
"Anith Sen" <anith@.bizdatasolutions.com> schrieb im Newsbeitrag
news:OOgsG%23LMFHA.3296@.TK2MSFTNGP15.phx.gbl...
> You can use the EXISTS() clause like:
> IF EXISTS( SELECT * FROM ... )
> --
> Anith
>
I hope I don't embarres myself because of the simplicity of this questrion,
but what would be the fastest way to determine, if a query would return any
rows (without actually returning them, and I don't need to know how many
there are, either)?
Thanks,
KlausYou can use the EXISTS() clause like:
IF EXISTS( SELECT * FROM ... )
--
Anith|||Thanks!
"Anith Sen" <anith@.bizdatasolutions.com> schrieb im Newsbeitrag
news:OOgsG%23LMFHA.3296@.TK2MSFTNGP15.phx.gbl...
> You can use the EXISTS() clause like:
> IF EXISTS( SELECT * FROM ... )
> --
> Anith
>
Fastest way to delete to huge datarow?
Hi all,
I've got a table which is storing Images with a size up to 1GB. When
I'm going to delete one of this rows the DELETE Statement takes up to 5
minutes. Which is the best way to delete the row more faster?
Has anyone an idea? Thanks in advance!
Gerrit Horeis
Software Developer
CI-Gate Development & Consulting GmbH
http://www.ci-gate.de
http://www.xira.de
http://www.bitbauer.deOn 04.12.2006 15:29, Gerrit Horeis wrote:
> I've got a table which is storing Images with a size up to 1GB. When
> I'm going to delete one of this rows the DELETE Statement takes up to 5
> minutes. Which is the best way to delete the row more faster?
> Has anyone an idea? Thanks in advance!
Did you measure what is slow there? Do you have an index on that table?
Is it used for deletion? ... With the little information you disclose
it's hard to come up with any reasonable advice.
Regards
robert|||Hi
Thanks for your answer. In the table im storing several image formats.
The delete Statement is
"Delete from Formats where ID = @.FormatID"
"ID" is the primary key of the table. Do I have to create an index as
well? I ever thought Sql just removes the reference to the data and so
it must be very fast.
Please help!
Robert Klemme schrieb:
> On 04.12.2006 15:29, Gerrit Horeis wrote:
>
> Did you measure what is slow there? Do you have an index on that table?
> Is it used for deletion? ... With the little information you disclose
> it's hard to come up with any reasonable advice.
> Regards
> robert|||On 05.12.2006 14:22, Gerrit Horeis wrote:
> Thanks for your answer. In the table im storing several image formats.
> The delete Statement is
> "Delete from Formats where ID = @.FormatID"
> "ID" is the primary key of the table. Do I have to create an index as
> well? I ever thought Sql just removes the reference to the data and so
> it must be very fast.
It seems you got the basics right. But storage of BLOBS is usually more
complex and less efficient than, say, VARCHAR columns. I guess you have
to run the Profiler to find out why it is actually slow.
Kind regards
robert
I've got a table which is storing Images with a size up to 1GB. When
I'm going to delete one of this rows the DELETE Statement takes up to 5
minutes. Which is the best way to delete the row more faster?
Has anyone an idea? Thanks in advance!
Gerrit Horeis
Software Developer
CI-Gate Development & Consulting GmbH
http://www.ci-gate.de
http://www.xira.de
http://www.bitbauer.deOn 04.12.2006 15:29, Gerrit Horeis wrote:
> I've got a table which is storing Images with a size up to 1GB. When
> I'm going to delete one of this rows the DELETE Statement takes up to 5
> minutes. Which is the best way to delete the row more faster?
> Has anyone an idea? Thanks in advance!
Did you measure what is slow there? Do you have an index on that table?
Is it used for deletion? ... With the little information you disclose
it's hard to come up with any reasonable advice.
Regards
robert|||Hi
Thanks for your answer. In the table im storing several image formats.
The delete Statement is
"Delete from Formats where ID = @.FormatID"
"ID" is the primary key of the table. Do I have to create an index as
well? I ever thought Sql just removes the reference to the data and so
it must be very fast.
Please help!
Robert Klemme schrieb:
> On 04.12.2006 15:29, Gerrit Horeis wrote:
>
> Did you measure what is slow there? Do you have an index on that table?
> Is it used for deletion? ... With the little information you disclose
> it's hard to come up with any reasonable advice.
> Regards
> robert|||On 05.12.2006 14:22, Gerrit Horeis wrote:
> Thanks for your answer. In the table im storing several image formats.
> The delete Statement is
> "Delete from Formats where ID = @.FormatID"
> "ID" is the primary key of the table. Do I have to create an index as
> well? I ever thought Sql just removes the reference to the data and so
> it must be very fast.
It seems you got the basics right. But storage of BLOBS is usually more
complex and less efficient than, say, VARCHAR columns. I guess you have
to run the Profiler to find out why it is actually slow.
Kind regards
robert
Fastest way to delete to huge datarow?
Hi all,
I've got a table which is storing Images with a size up to 1GB. When
I'm going to delete one of this rows the DELETE Statement takes up to 5
minutes. Which is the best way to delete the row more faster?
Has anyone an idea? Thanks in advance!
Gerrit Horeis
Software Developer
CI-Gate Development & Consulting GmbH
http://www.ci-gate.de
http://www.xira.de
http://www.bitbauer.de
Hi
Thanks for your answer. In the table im storing several image formats.
The delete Statement is
"Delete from Formats where ID = @.FormatID"
"ID" is the primary key of the table. Do I have to create an index as
well? I ever thought Sql just removes the reference to the data and so
it must be very fast.
Please help!
Robert Klemme schrieb:
> On 04.12.2006 15:29, Gerrit Horeis wrote:
>
> Did you measure what is slow there? Do you have an index on that table?
> Is it used for deletion? ... With the little information you disclose
> it's hard to come up with any reasonable advice.
> Regards
> robert
I've got a table which is storing Images with a size up to 1GB. When
I'm going to delete one of this rows the DELETE Statement takes up to 5
minutes. Which is the best way to delete the row more faster?
Has anyone an idea? Thanks in advance!
Gerrit Horeis
Software Developer
CI-Gate Development & Consulting GmbH
http://www.ci-gate.de
http://www.xira.de
http://www.bitbauer.de
Hi
Thanks for your answer. In the table im storing several image formats.
The delete Statement is
"Delete from Formats where ID = @.FormatID"
"ID" is the primary key of the table. Do I have to create an index as
well? I ever thought Sql just removes the reference to the data and so
it must be very fast.
Please help!
Robert Klemme schrieb:
> On 04.12.2006 15:29, Gerrit Horeis wrote:
>
> Did you measure what is slow there? Do you have an index on that table?
> Is it used for deletion? ... With the little information you disclose
> it's hard to come up with any reasonable advice.
> Regards
> robert
Fastest way to delete to huge datarow?
Hi all,
I've got a table which is storing Images with a size up to 1GB. When
I'm going to delete one of this rows the DELETE Statement takes up to 5
minutes. Which is the best way to delete the row more faster?
Has anyone an idea? Thanks in advance!
--
Gerrit Horeis
Software Developer
CI-Gate Development & Consulting GmbH
http://www.ci-gate.de
http://www.xira.de
http://www.bitbauer.deOn 04.12.2006 15:29, Gerrit Horeis wrote:
> I've got a table which is storing Images with a size up to 1GB. When
> I'm going to delete one of this rows the DELETE Statement takes up to 5
> minutes. Which is the best way to delete the row more faster?
> Has anyone an idea? Thanks in advance!
Did you measure what is slow there? Do you have an index on that table?
Is it used for deletion? ... With the little information you disclose
it's hard to come up with any reasonable advice.
Regards
robert|||Hi
Thanks for your answer. In the table im storing several image formats.
The delete Statement is
"Delete from Formats where ID = @.FormatID"
"ID" is the primary key of the table. Do I have to create an index as
well? I ever thought Sql just removes the reference to the data and so
it must be very fast.
Please help!
Robert Klemme schrieb:
> On 04.12.2006 15:29, Gerrit Horeis wrote:
> > I've got a table which is storing Images with a size up to 1GB. When
> > I'm going to delete one of this rows the DELETE Statement takes up to 5
> >
> > minutes. Which is the best way to delete the row more faster?
> >
> > Has anyone an idea? Thanks in advance!
> Did you measure what is slow there? Do you have an index on that table?
> Is it used for deletion? ... With the little information you disclose
> it's hard to come up with any reasonable advice.
> Regards
> robert|||On 05.12.2006 14:22, Gerrit Horeis wrote:
> Thanks for your answer. In the table im storing several image formats.
> The delete Statement is
> "Delete from Formats where ID = @.FormatID"
> "ID" is the primary key of the table. Do I have to create an index as
> well? I ever thought Sql just removes the reference to the data and so
> it must be very fast.
It seems you got the basics right. But storage of BLOBS is usually more
complex and less efficient than, say, VARCHAR columns. I guess you have
to run the Profiler to find out why it is actually slow.
Kind regards
robert
I've got a table which is storing Images with a size up to 1GB. When
I'm going to delete one of this rows the DELETE Statement takes up to 5
minutes. Which is the best way to delete the row more faster?
Has anyone an idea? Thanks in advance!
--
Gerrit Horeis
Software Developer
CI-Gate Development & Consulting GmbH
http://www.ci-gate.de
http://www.xira.de
http://www.bitbauer.deOn 04.12.2006 15:29, Gerrit Horeis wrote:
> I've got a table which is storing Images with a size up to 1GB. When
> I'm going to delete one of this rows the DELETE Statement takes up to 5
> minutes. Which is the best way to delete the row more faster?
> Has anyone an idea? Thanks in advance!
Did you measure what is slow there? Do you have an index on that table?
Is it used for deletion? ... With the little information you disclose
it's hard to come up with any reasonable advice.
Regards
robert|||Hi
Thanks for your answer. In the table im storing several image formats.
The delete Statement is
"Delete from Formats where ID = @.FormatID"
"ID" is the primary key of the table. Do I have to create an index as
well? I ever thought Sql just removes the reference to the data and so
it must be very fast.
Please help!
Robert Klemme schrieb:
> On 04.12.2006 15:29, Gerrit Horeis wrote:
> > I've got a table which is storing Images with a size up to 1GB. When
> > I'm going to delete one of this rows the DELETE Statement takes up to 5
> >
> > minutes. Which is the best way to delete the row more faster?
> >
> > Has anyone an idea? Thanks in advance!
> Did you measure what is slow there? Do you have an index on that table?
> Is it used for deletion? ... With the little information you disclose
> it's hard to come up with any reasonable advice.
> Regards
> robert|||On 05.12.2006 14:22, Gerrit Horeis wrote:
> Thanks for your answer. In the table im storing several image formats.
> The delete Statement is
> "Delete from Formats where ID = @.FormatID"
> "ID" is the primary key of the table. Do I have to create an index as
> well? I ever thought Sql just removes the reference to the data and so
> it must be very fast.
It seems you got the basics right. But storage of BLOBS is usually more
complex and less efficient than, say, VARCHAR columns. I guess you have
to run the Profiler to find out why it is actually slow.
Kind regards
robert
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
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
Faster Query
Two quick questions,
1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
table .
2. Does anyone knows what is the equivalent command for "Instant File
Initilization"
in SQL Server 2005 for faster database / file creation, in SQL Server
2000...'
Regards.
Piku.1. Do it in steps, say 1000 to 10000 rows at a time, so not all rows are deleted in a single
transaction.
2. There is no such thing in 2000. Don't over-use autogrow and be prepared that it takes time to
create database and expand database files.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Piku" <Piku@.discussions.microsoft.com> wrote in message
news:93258D03-424F-412E-9624-82864426F6A5@.microsoft.com...
> Two quick questions,
> 1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
> table .
> 2. Does anyone knows what is the equivalent command for "Instant File
> Initilization"
> in SQL Server 2005 for faster database / file creation, in SQL Server
> 2000...'
> Regards.
> Piku.
>|||> 1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
> table .
You'll usually get the best performance and keep the transaction log size
reasonable by deleting data in smaller batches. The details on how to do
this efficiently depend your actual situation (indexes, delete criteria) and
the version of SQL Server. A SQL 2005 example:
DECLARE @.RowsDeleted int
WHILE @.RowsDeleted IS NULL OR @.RowsDeleted > 0
BEGIN
DELETE TOP (1000000)
FROM dbo.MyTable
WHERE CreateDate < '20060101'
SET @.RowsDeleted = @.@.ROWCOUNT
END
> 2. Does anyone knows what is the equivalent command for "Instant File
> Initilization"
> in SQL Server 2005 for faster database / file creation, in SQL Server
> 2000...'
This is a new feature introduced in SQL 2005 and is therefore not available
in SQL 2000.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Piku" <Piku@.discussions.microsoft.com> wrote in message
news:93258D03-424F-412E-9624-82864426F6A5@.microsoft.com...
> Two quick questions,
> 1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
> table .
> 2. Does anyone knows what is the equivalent command for "Instant File
> Initilization"
> in SQL Server 2005 for faster database / file creation, in SQL Server
> 2000...'
> Regards.
> Piku.
>|||Tibor, Thx for the quick reply.
1. I am deleting 5000 at a time , but for deleting 10 mil, rows it takes
about 5 hours.
here is the script I am using, please suggest me if you have something better:
Use database
GO
while 1=1
begin
set rowcount 5000
DELETE from table_name
where DATE_TIME < '2006-03-01 00:00:00.000'
IF @.@.ROWCOUNT = 0
BREAK
end
set rowcount 0
2. Are you sure there is no FASTER way of creating a DB / File...' Because
it really matters, when you want to create a DB / File of about 200-300 gb.
"Tibor Karaszi" wrote:
> 1. Do it in steps, say 1000 to 10000 rows at a time, so not all rows are deleted in a single
> transaction.
> 2. There is no such thing in 2000. Don't over-use autogrow and be prepared that it takes time to
> create database and expand database files.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Piku" <Piku@.discussions.microsoft.com> wrote in message
> news:93258D03-424F-412E-9624-82864426F6A5@.microsoft.com...
> > Two quick questions,
> >
> > 1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
> > table .
> >
> > 2. Does anyone knows what is the equivalent command for "Instant File
> > Initilization"
> > in SQL Server 2005 for faster database / file creation, in SQL Server
> > 2000...'
> >
> > Regards.
> >
> > Piku.
> >
> >
>|||> 2. Are you sure there is no FASTER way of creating a DB / File...' Because
> it really matters, when you want to create a DB / File of about 200-300 gb.
That would be better I/O throughput...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Piku" <Piku@.discussions.microsoft.com> wrote in message
news:57419BC5-2016-4C80-8BC8-0621B7FCD6D1@.microsoft.com...
> Tibor, Thx for the quick reply.
> 1. I am deleting 5000 at a time , but for deleting 10 mil, rows it takes
> about 5 hours.
> here is the script I am using, please suggest me if you have something better:
> Use database
> GO
> while 1=1
> begin
> set rowcount 5000
> DELETE from table_name
> where DATE_TIME < '2006-03-01 00:00:00.000'
> IF @.@.ROWCOUNT = 0
> BREAK
> end
> set rowcount 0
> 2. Are you sure there is no FASTER way of creating a DB / File...' Because
> it really matters, when you want to create a DB / File of about 200-300 gb.
>
>
> "Tibor Karaszi" wrote:
>> 1. Do it in steps, say 1000 to 10000 rows at a time, so not all rows are deleted in a single
>> transaction.
>> 2. There is no such thing in 2000. Don't over-use autogrow and be prepared that it takes time to
>> create database and expand database files.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Piku" <Piku@.discussions.microsoft.com> wrote in message
>> news:93258D03-424F-412E-9624-82864426F6A5@.microsoft.com...
>> > Two quick questions,
>> >
>> > 1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
>> > table .
>> >
>> > 2. Does anyone knows what is the equivalent command for "Instant File
>> > Initilization"
>> > in SQL Server 2005 for faster database / file creation, in SQL Server
>> > 2000...'
>> >
>> > Regards.
>> >
>> > Piku.
>> >
>> >
>>|||"Piku" <Piku@.discussions.microsoft.com> wrote in message
news:57419BC5-2016-4C80-8BC8-0621B7FCD6D1@.microsoft.com...
> 2. Are you sure there is no FASTER way of creating a DB / File...'
Because
> it really matters, when you want to create a DB / File of about 200-300
gb.
>
Not within SQL Server.
However, some SAN solutions like I believe Left Hand Network's solution will
do this at a hardware level.|||"Piku" <Piku@.discussions.microsoft.com> wrote in message
news:93258D03-424F-412E-9624-82864426F6A5@.microsoft.com...
> Two quick questions,
> 1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
> table .
Well if you want to delete ALL the rows, truncate table is the answer.
Another option if you want to keep some records and the number of records is
relatively small is to select them into a new table, drop the old table and
then rename the new table to the old table name.
> 2. Does anyone knows what is the equivalent command for "Instant File
> Initilization"
> in SQL Server 2005 for faster database / file creation, in SQL Server
> 2000...'
> Regards.
> Piku.
>
1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
table .
2. Does anyone knows what is the equivalent command for "Instant File
Initilization"
in SQL Server 2005 for faster database / file creation, in SQL Server
2000...'
Regards.
Piku.1. Do it in steps, say 1000 to 10000 rows at a time, so not all rows are deleted in a single
transaction.
2. There is no such thing in 2000. Don't over-use autogrow and be prepared that it takes time to
create database and expand database files.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Piku" <Piku@.discussions.microsoft.com> wrote in message
news:93258D03-424F-412E-9624-82864426F6A5@.microsoft.com...
> Two quick questions,
> 1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
> table .
> 2. Does anyone knows what is the equivalent command for "Instant File
> Initilization"
> in SQL Server 2005 for faster database / file creation, in SQL Server
> 2000...'
> Regards.
> Piku.
>|||> 1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
> table .
You'll usually get the best performance and keep the transaction log size
reasonable by deleting data in smaller batches. The details on how to do
this efficiently depend your actual situation (indexes, delete criteria) and
the version of SQL Server. A SQL 2005 example:
DECLARE @.RowsDeleted int
WHILE @.RowsDeleted IS NULL OR @.RowsDeleted > 0
BEGIN
DELETE TOP (1000000)
FROM dbo.MyTable
WHERE CreateDate < '20060101'
SET @.RowsDeleted = @.@.ROWCOUNT
END
> 2. Does anyone knows what is the equivalent command for "Instant File
> Initilization"
> in SQL Server 2005 for faster database / file creation, in SQL Server
> 2000...'
This is a new feature introduced in SQL 2005 and is therefore not available
in SQL 2000.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Piku" <Piku@.discussions.microsoft.com> wrote in message
news:93258D03-424F-412E-9624-82864426F6A5@.microsoft.com...
> Two quick questions,
> 1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
> table .
> 2. Does anyone knows what is the equivalent command for "Instant File
> Initilization"
> in SQL Server 2005 for faster database / file creation, in SQL Server
> 2000...'
> Regards.
> Piku.
>|||Tibor, Thx for the quick reply.
1. I am deleting 5000 at a time , but for deleting 10 mil, rows it takes
about 5 hours.
here is the script I am using, please suggest me if you have something better:
Use database
GO
while 1=1
begin
set rowcount 5000
DELETE from table_name
where DATE_TIME < '2006-03-01 00:00:00.000'
IF @.@.ROWCOUNT = 0
BREAK
end
set rowcount 0
2. Are you sure there is no FASTER way of creating a DB / File...' Because
it really matters, when you want to create a DB / File of about 200-300 gb.
"Tibor Karaszi" wrote:
> 1. Do it in steps, say 1000 to 10000 rows at a time, so not all rows are deleted in a single
> transaction.
> 2. There is no such thing in 2000. Don't over-use autogrow and be prepared that it takes time to
> create database and expand database files.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Piku" <Piku@.discussions.microsoft.com> wrote in message
> news:93258D03-424F-412E-9624-82864426F6A5@.microsoft.com...
> > Two quick questions,
> >
> > 1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
> > table .
> >
> > 2. Does anyone knows what is the equivalent command for "Instant File
> > Initilization"
> > in SQL Server 2005 for faster database / file creation, in SQL Server
> > 2000...'
> >
> > Regards.
> >
> > Piku.
> >
> >
>|||> 2. Are you sure there is no FASTER way of creating a DB / File...' Because
> it really matters, when you want to create a DB / File of about 200-300 gb.
That would be better I/O throughput...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Piku" <Piku@.discussions.microsoft.com> wrote in message
news:57419BC5-2016-4C80-8BC8-0621B7FCD6D1@.microsoft.com...
> Tibor, Thx for the quick reply.
> 1. I am deleting 5000 at a time , but for deleting 10 mil, rows it takes
> about 5 hours.
> here is the script I am using, please suggest me if you have something better:
> Use database
> GO
> while 1=1
> begin
> set rowcount 5000
> DELETE from table_name
> where DATE_TIME < '2006-03-01 00:00:00.000'
> IF @.@.ROWCOUNT = 0
> BREAK
> end
> set rowcount 0
> 2. Are you sure there is no FASTER way of creating a DB / File...' Because
> it really matters, when you want to create a DB / File of about 200-300 gb.
>
>
> "Tibor Karaszi" wrote:
>> 1. Do it in steps, say 1000 to 10000 rows at a time, so not all rows are deleted in a single
>> transaction.
>> 2. There is no such thing in 2000. Don't over-use autogrow and be prepared that it takes time to
>> create database and expand database files.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Piku" <Piku@.discussions.microsoft.com> wrote in message
>> news:93258D03-424F-412E-9624-82864426F6A5@.microsoft.com...
>> > Two quick questions,
>> >
>> > 1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
>> > table .
>> >
>> > 2. Does anyone knows what is the equivalent command for "Instant File
>> > Initilization"
>> > in SQL Server 2005 for faster database / file creation, in SQL Server
>> > 2000...'
>> >
>> > Regards.
>> >
>> > Piku.
>> >
>> >
>>|||"Piku" <Piku@.discussions.microsoft.com> wrote in message
news:57419BC5-2016-4C80-8BC8-0621B7FCD6D1@.microsoft.com...
> 2. Are you sure there is no FASTER way of creating a DB / File...'
Because
> it really matters, when you want to create a DB / File of about 200-300
gb.
>
Not within SQL Server.
However, some SAN solutions like I believe Left Hand Network's solution will
do this at a hardware level.|||"Piku" <Piku@.discussions.microsoft.com> wrote in message
news:93258D03-424F-412E-9624-82864426F6A5@.microsoft.com...
> Two quick questions,
> 1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
> table .
Well if you want to delete ALL the rows, truncate table is the answer.
Another option if you want to keep some records and the number of records is
relatively small is to select them into a new table, drop the old table and
then rename the new table to the old table name.
> 2. Does anyone knows what is the equivalent command for "Instant File
> Initilization"
> in SQL Server 2005 for faster database / file creation, in SQL Server
> 2000...'
> Regards.
> Piku.
>
Faster Query
Two quick questions,
1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
table .
2. Does anyone knows what is the equivalent command for "Instant File
Initilization"
in SQL Server 2005 for faster database / file creation, in SQL Server
2000...'
Regards.
Piku.1. Do it in steps, say 1000 to 10000 rows at a time, so not all rows are del
eted in a single
transaction.
2. There is no such thing in 2000. Don't over-use autogrow and be prepared t
hat it takes time to
create database and expand database files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Piku" <Piku@.discussions.microsoft.com> wrote in message
news:93258D03-424F-412E-9624-82864426F6A5@.microsoft.com...
> Two quick questions,
> 1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
> table .
> 2. Does anyone knows what is the equivalent command for "Instant File
> Initilization"
> in SQL Server 2005 for faster database / file creation, in SQL Server
> 2000...'
> Regards.
> Piku.
>|||> 1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
> table .
You'll usually get the best performance and keep the transaction log size
reasonable by deleting data in smaller batches. The details on how to do
this efficiently depend your actual situation (indexes, delete criteria) and
the version of SQL Server. A SQL 2005 example:
DECLARE @.RowsDeleted int
WHILE @.RowsDeleted IS NULL OR @.RowsDeleted > 0
BEGIN
DELETE TOP (1000000)
FROM dbo.MyTable
WHERE CreateDate < '20060101'
SET @.RowsDeleted = @.@.ROWCOUNT
END
> 2. Does anyone knows what is the equivalent command for "Instant File
> Initilization"
> in SQL Server 2005 for faster database / file creation, in SQL Server
> 2000...'
This is a new feature introduced in SQL 2005 and is therefore not available
in SQL 2000.
Hope this helps.
Dan Guzman
SQL Server MVP
"Piku" <Piku@.discussions.microsoft.com> wrote in message
news:93258D03-424F-412E-9624-82864426F6A5@.microsoft.com...
> Two quick questions,
> 1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
> table .
> 2. Does anyone knows what is the equivalent command for "Instant File
> Initilization"
> in SQL Server 2005 for faster database / file creation, in SQL Server
> 2000...'
> Regards.
> Piku.
>|||Tibor, Thx for the quick reply.
1. I am deleting 5000 at a time , but for deleting 10 mil, rows it takes
about 5 hours.
here is the script I am using, please suggest me if you have something bette
r:
Use database
GO
while 1=1
begin
set rowcount 5000
DELETE from table_name
where DATE_TIME < '2006-03-01 00:00:00.000'
IF @.@.ROWCOUNT = 0
BREAK
end
set rowcount 0
2. Are you sure there is no FASTER way of creating a DB / File...' Because
it really matters, when you want to create a DB / File of about 200-300 gb.
"Tibor Karaszi" wrote:
> 1. Do it in steps, say 1000 to 10000 rows at a time, so not all rows are d
eleted in a single
> transaction.
> 2. There is no such thing in 2000. Don't over-use autogrow and be prepared
that it takes time to
> create database and expand database files.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Piku" <Piku@.discussions.microsoft.com> wrote in message
> news:93258D03-424F-412E-9624-82864426F6A5@.microsoft.com...
>|||> 2. Are you sure there is no FASTER way of creating a DB / File...' Because[vbcol=seagreen
]
> it really matters, when you want to create a DB / File of about 200-300 gb.[/vbcol
]
That would be better I/O throughput...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Piku" <Piku@.discussions.microsoft.com> wrote in message
news:57419BC5-2016-4C80-8BC8-0621B7FCD6D1@.microsoft.com...[vbcol=seagreen]
> Tibor, Thx for the quick reply.
> 1. I am deleting 5000 at a time , but for deleting 10 mil, rows it takes
> about 5 hours.
> here is the script I am using, please suggest me if you have something bet
ter:
> Use database
> GO
> while 1=1
> begin
> set rowcount 5000
> DELETE from table_name
> where DATE_TIME < '2006-03-01 00:00:00.000'
> IF @.@.ROWCOUNT = 0
> BREAK
> end
> set rowcount 0
> 2. Are you sure there is no FASTER way of creating a DB / File...' Becaus
e
> it really matters, when you want to create a DB / File of about 200-300 gb
.
>
>
> "Tibor Karaszi" wrote:
>|||"Piku" <Piku@.discussions.microsoft.com> wrote in message
news:57419BC5-2016-4C80-8BC8-0621B7FCD6D1@.microsoft.com...
> 2. Are you sure there is no FASTER way of creating a DB / File...'
Because
> it really matters, when you want to create a DB / File of about 200-300
gb.
>
Not within SQL Server.
However, some SAN solutions like I believe Left Hand Network's solution will
do this at a hardware level.|||"Piku" <Piku@.discussions.microsoft.com> wrote in message
news:93258D03-424F-412E-9624-82864426F6A5@.microsoft.com...
> Two quick questions,
> 1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
> table .
Well if you want to delete ALL the rows, truncate table is the answer.
Another option if you want to keep some records and the number of records is
relatively small is to select them into a new table, drop the old table and
then rename the new table to the old table name.
> 2. Does anyone knows what is the equivalent command for "Instant File
> Initilization"
> in SQL Server 2005 for faster database / file creation, in SQL Server
> 2000...'
> Regards.
> Piku.
>
1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
table .
2. Does anyone knows what is the equivalent command for "Instant File
Initilization"
in SQL Server 2005 for faster database / file creation, in SQL Server
2000...'
Regards.
Piku.1. Do it in steps, say 1000 to 10000 rows at a time, so not all rows are del
eted in a single
transaction.
2. There is no such thing in 2000. Don't over-use autogrow and be prepared t
hat it takes time to
create database and expand database files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Piku" <Piku@.discussions.microsoft.com> wrote in message
news:93258D03-424F-412E-9624-82864426F6A5@.microsoft.com...
> Two quick questions,
> 1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
> table .
> 2. Does anyone knows what is the equivalent command for "Instant File
> Initilization"
> in SQL Server 2005 for faster database / file creation, in SQL Server
> 2000...'
> Regards.
> Piku.
>|||> 1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
> table .
You'll usually get the best performance and keep the transaction log size
reasonable by deleting data in smaller batches. The details on how to do
this efficiently depend your actual situation (indexes, delete criteria) and
the version of SQL Server. A SQL 2005 example:
DECLARE @.RowsDeleted int
WHILE @.RowsDeleted IS NULL OR @.RowsDeleted > 0
BEGIN
DELETE TOP (1000000)
FROM dbo.MyTable
WHERE CreateDate < '20060101'
SET @.RowsDeleted = @.@.ROWCOUNT
END
> 2. Does anyone knows what is the equivalent command for "Instant File
> Initilization"
> in SQL Server 2005 for faster database / file creation, in SQL Server
> 2000...'
This is a new feature introduced in SQL 2005 and is therefore not available
in SQL 2000.
Hope this helps.
Dan Guzman
SQL Server MVP
"Piku" <Piku@.discussions.microsoft.com> wrote in message
news:93258D03-424F-412E-9624-82864426F6A5@.microsoft.com...
> Two quick questions,
> 1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
> table .
> 2. Does anyone knows what is the equivalent command for "Instant File
> Initilization"
> in SQL Server 2005 for faster database / file creation, in SQL Server
> 2000...'
> Regards.
> Piku.
>|||Tibor, Thx for the quick reply.
1. I am deleting 5000 at a time , but for deleting 10 mil, rows it takes
about 5 hours.
here is the script I am using, please suggest me if you have something bette
r:
Use database
GO
while 1=1
begin
set rowcount 5000
DELETE from table_name
where DATE_TIME < '2006-03-01 00:00:00.000'
IF @.@.ROWCOUNT = 0
BREAK
end
set rowcount 0
2. Are you sure there is no FASTER way of creating a DB / File...' Because
it really matters, when you want to create a DB / File of about 200-300 gb.
"Tibor Karaszi" wrote:
> 1. Do it in steps, say 1000 to 10000 rows at a time, so not all rows are d
eleted in a single
> transaction.
> 2. There is no such thing in 2000. Don't over-use autogrow and be prepared
that it takes time to
> create database and expand database files.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Piku" <Piku@.discussions.microsoft.com> wrote in message
> news:93258D03-424F-412E-9624-82864426F6A5@.microsoft.com...
>|||> 2. Are you sure there is no FASTER way of creating a DB / File...' Because[vbcol=seagreen
]
> it really matters, when you want to create a DB / File of about 200-300 gb.[/vbcol
]
That would be better I/O throughput...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Piku" <Piku@.discussions.microsoft.com> wrote in message
news:57419BC5-2016-4C80-8BC8-0621B7FCD6D1@.microsoft.com...[vbcol=seagreen]
> Tibor, Thx for the quick reply.
> 1. I am deleting 5000 at a time , but for deleting 10 mil, rows it takes
> about 5 hours.
> here is the script I am using, please suggest me if you have something bet
ter:
> Use database
> GO
> while 1=1
> begin
> set rowcount 5000
> DELETE from table_name
> where DATE_TIME < '2006-03-01 00:00:00.000'
> IF @.@.ROWCOUNT = 0
> BREAK
> end
> set rowcount 0
> 2. Are you sure there is no FASTER way of creating a DB / File...' Becaus
e
> it really matters, when you want to create a DB / File of about 200-300 gb
.
>
>
> "Tibor Karaszi" wrote:
>|||"Piku" <Piku@.discussions.microsoft.com> wrote in message
news:57419BC5-2016-4C80-8BC8-0621B7FCD6D1@.microsoft.com...
> 2. Are you sure there is no FASTER way of creating a DB / File...'
Because
> it really matters, when you want to create a DB / File of about 200-300
gb.
>
Not within SQL Server.
However, some SAN solutions like I believe Left Hand Network's solution will
do this at a hardware level.|||"Piku" <Piku@.discussions.microsoft.com> wrote in message
news:93258D03-424F-412E-9624-82864426F6A5@.microsoft.com...
> Two quick questions,
> 1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
> table .
Well if you want to delete ALL the rows, truncate table is the answer.
Another option if you want to keep some records and the number of records is
relatively small is to select them into a new table, drop the old table and
then rename the new table to the old table name.
> 2. Does anyone knows what is the equivalent command for "Instant File
> Initilization"
> in SQL Server 2005 for faster database / file creation, in SQL Server
> 2000...'
> Regards.
> Piku.
>
Wednesday, March 7, 2012
faster count(*) or alternative
hi,
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.
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...
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?
>
>
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.
Define slow?
Yes, post a full DDL.
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||C10B (tswalton@.gmail.com) writes:
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.
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.
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
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?
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
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.
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.
--
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
faster count(*) or alternative
hi,
First question, do you have an index on the table.
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||C10B (tswalton@.gmail.com) writes:
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.
little better
DDL = Data Definition Language. That is, CREATE TABLE and CREATE INDEX and
the like.
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.
>
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.
>
>
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.
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
>
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 Query
Hi,
What the best and fast way to check if a table has rows?
ThanksTry,
if exists(select * from dbo.t1)
print 'has rows.'
else
print 'no row'
AMB
"Chris" wrote:
> Hi,
> What the best and fast way to check if a table has rows?
> Thanks|||id have to disagree
if exists(select top 1 field1 from dbo.t1)
> print 'has rows.'
> else
> print 'no row'
"Alejandro Mesa" wrote:
> Try,
> if exists(select * from dbo.t1)
> print 'has rows.'
> else
> print 'no row'
>
> AMB
> "Chris" wrote:
> > Hi,
> > What the best and fast way to check if a table has rows?
> >
> > Thanks|||The difference between Alejandro and Michael's solutions is extremely small.
Alejandro provides the standard solution. It's efficient, easy to read, and
transportable. Michaels solution may in some limited situations be ever so
slightly more efficient but we are taking about a single page read.
Chris,
Go with Alejandro's solution... You'll be glad you did.
"Michael Evanchik" <MichaelEvanchik@.discussions.microsoft.com> wrote in
message news:65610958-F782-4F2B-B07B-EB80E65C7C80@.microsoft.com...
> id have to disagree
> if exists(select top 1 field1 from dbo.t1)
>> print 'has rows.'
>> else
>> print 'no row'
>
> "Alejandro Mesa" wrote:
>> Try,
>> if exists(select * from dbo.t1)
>> print 'has rows.'
>> else
>> print 'no row'
>>
>> AMB
>> "Chris" wrote:
>> > Hi,
>> > What the best and fast way to check if a table has rows?
>> >
>> > Thanks|||Actually I think that way has more of a potential to do more work. When you
use IF EXISTS with a SELECT * you give sql server the choice of what column
or index to use to determine if the row exists. If you specify a column
name it has to use that column. There is no need to use TOP 1 since EXISTS
will always stop after the first match is found.
--
Andrew J. Kelly SQL MVP
"Michael Evanchik" <MichaelEvanchik@.discussions.microsoft.com> wrote in
message news:65610958-F782-4F2B-B07B-EB80E65C7C80@.microsoft.com...
> id have to disagree
> if exists(select top 1 field1 from dbo.t1)
>> print 'has rows.'
>> else
>> print 'no row'
>
> "Alejandro Mesa" wrote:
>> Try,
>> if exists(select * from dbo.t1)
>> print 'has rows.'
>> else
>> print 'no row'
>>
>> AMB
>> "Chris" wrote:
>> > Hi,
>> > What the best and fast way to check if a table has rows?
>> >
>> > Thanks|||Thanks guys!
"Chris" wrote:
> Hi,
> What the best and fast way to check if a table has rows?
> Thanks
What the best and fast way to check if a table has rows?
ThanksTry,
if exists(select * from dbo.t1)
print 'has rows.'
else
print 'no row'
AMB
"Chris" wrote:
> Hi,
> What the best and fast way to check if a table has rows?
> Thanks|||id have to disagree
if exists(select top 1 field1 from dbo.t1)
> print 'has rows.'
> else
> print 'no row'
"Alejandro Mesa" wrote:
> Try,
> if exists(select * from dbo.t1)
> print 'has rows.'
> else
> print 'no row'
>
> AMB
> "Chris" wrote:
> > Hi,
> > What the best and fast way to check if a table has rows?
> >
> > Thanks|||The difference between Alejandro and Michael's solutions is extremely small.
Alejandro provides the standard solution. It's efficient, easy to read, and
transportable. Michaels solution may in some limited situations be ever so
slightly more efficient but we are taking about a single page read.
Chris,
Go with Alejandro's solution... You'll be glad you did.
"Michael Evanchik" <MichaelEvanchik@.discussions.microsoft.com> wrote in
message news:65610958-F782-4F2B-B07B-EB80E65C7C80@.microsoft.com...
> id have to disagree
> if exists(select top 1 field1 from dbo.t1)
>> print 'has rows.'
>> else
>> print 'no row'
>
> "Alejandro Mesa" wrote:
>> Try,
>> if exists(select * from dbo.t1)
>> print 'has rows.'
>> else
>> print 'no row'
>>
>> AMB
>> "Chris" wrote:
>> > Hi,
>> > What the best and fast way to check if a table has rows?
>> >
>> > Thanks|||Actually I think that way has more of a potential to do more work. When you
use IF EXISTS with a SELECT * you give sql server the choice of what column
or index to use to determine if the row exists. If you specify a column
name it has to use that column. There is no need to use TOP 1 since EXISTS
will always stop after the first match is found.
--
Andrew J. Kelly SQL MVP
"Michael Evanchik" <MichaelEvanchik@.discussions.microsoft.com> wrote in
message news:65610958-F782-4F2B-B07B-EB80E65C7C80@.microsoft.com...
> id have to disagree
> if exists(select top 1 field1 from dbo.t1)
>> print 'has rows.'
>> else
>> print 'no row'
>
> "Alejandro Mesa" wrote:
>> Try,
>> if exists(select * from dbo.t1)
>> print 'has rows.'
>> else
>> print 'no row'
>>
>> AMB
>> "Chris" wrote:
>> > Hi,
>> > What the best and fast way to check if a table has rows?
>> >
>> > Thanks|||Thanks guys!
"Chris" wrote:
> Hi,
> What the best and fast way to check if a table has rows?
> Thanks
fast loading of data to table
Hi Every one,
How can I load or copy say millions of rows to a table in the database faster?
Thanks,
Mejo GeorgeGive a look to BCP, BULK INSERT or Integration Services (SSIS).|||Expanding on Kent's suggestions, you may wish to remove or disable indexing for the duration of the import, and the rebuild the indexes -if usage permits.|||Thanks Kent & Arnie !!|||Along with these suggestions, you could look at changing your recovery model to the bulk-logged model for the duration of your loading.|||Tks
Subscribe to:
Posts (Atom)