MS SQL Server - Handling 11 Million Records

Author

Handling 11 Million Records

mymobile

2005-07-27, 7:23 am

Hello!

First of all the Specs:
SQL Server 2000 Enterprise Edition
2GB RAM
3.2Ghz Xeon Processor
80GB HDD

The problem:
Encountering "Timeout Expired" error message everytime I query a specified
date for deletion. Currently my Sales DB has 11 Million Records containing
all the transactions of a supermarket this is only just 9 months worth of
data!
However, when I add a record, there is no problem. Are there any other ways
to solve this problem? Would it be recommended to group my sales transactions
in 1table/month or its just ok to crowd them in 1 table? What are the
advantages and disadvantages?

Jacco Schalkwijk

2005-07-27, 9:23 am

Sounds like you need to create an index on the column(s) that you query. If
you don't have much experience with index design, you can use the Index
Tuning Wizard (Ctrl+I in Query Analyzer) to give you recommendations for the
correct indexes for your query.

--
Jacco Schalkwijk
SQL Server MVP

"mymobile" < mymobile@discussions
.microsoft.com> wrote in message
news:4896B27F-1062-4D0D-9D1B- E317EC6353C9@microso
ft.com...
> Hello!
>
> First of all the Specs:
> SQL Server 2000 Enterprise Edition
> 2GB RAM
> 3.2Ghz Xeon Processor
> 80GB HDD
>
> The problem:
> Encountering "Timeout Expired" error message everytime I query a specified
> date for deletion. Currently my Sales DB has 11 Million Records containing
> all the transactions of a supermarket this is only just 9 months worth of
> data!
> However, when I add a record, there is no problem. Are there any other
> ways
> to solve this problem? Would it be recommended to group my sales
> transactions
> in 1table/month or its just ok to crowd them in 1 table? What are the
> advantages and disadvantages?

mymobile

2005-07-27, 9:23 am

So it would be I will write my query to the Query Analyzer and press Ctrl+I
right?
And also just for verification, its ok to handle 11 Million Records on 1
table?

Thanks for the help.

"Jacco Schalkwijk" wrote:

> Sounds like you need to create an index on the column(s) that you query. If
> you don't have much experience with index design, you can use the Index
> Tuning Wizard (Ctrl+I in Query Analyzer) to give you recommendations for the
> correct indexes for your query.
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
>
> "mymobile" < mymobile@discussions
.microsoft.com> wrote in message
> news:4896B27F-1062-4D0D-9D1B- E317EC6353C9@microso
ft.com...
>
>
>

Mike Epprecht (SQL MVP)

2005-07-27, 9:23 am

Hi

Do you have appropriate indexes in place?
What does your table look like?
Are you doing your deletes in smaller batches to help concurrency?

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"mymobile" wrote:

> Hello!
>
> First of all the Specs:
> SQL Server 2000 Enterprise Edition
> 2GB RAM
> 3.2Ghz Xeon Processor
> 80GB HDD
>
> The problem:
> Encountering "Timeout Expired" error message everytime I query a specified
> date for deletion. Currently my Sales DB has 11 Million Records containing
> all the transactions of a supermarket this is only just 9 months worth of
> data!
> However, when I add a record, there is no problem. Are there any other ways
> to solve this problem? Would it be recommended to group my sales transactions
> in 1table/month or its just ok to crowd them in 1 table? What are the
> advantages and disadvantages?

Jacco Schalkwijk

2005-07-27, 9:23 am

Yep, just write the query in Query Analyzer and press Ctrl+I. You can
basically click next a few times and then choose "Select all tables" and
that's all you need for decent results. It's a good idea to run this against
a test or development copy of your database, as it will have an impact on
the server.

There is nothing wrong with 11 million rows in a table, as long as it is
indexed well. People work with tables with hundreds of millions of rows.

--
Jacco Schalkwijk
SQL Server MVP

"mymobile" < mymobile@discussions
.microsoft.com> wrote in message
news:0288FE27-A6E2-4C84-A900- D29C217861F7@microso
ft.com...[color=darkred]
> So it would be I will write my query to the Query Analyzer and press
> Ctrl+I
> right?
> And also just for verification, its ok to handle 11 Million Records on 1
> table?
>
> Thanks for the help.
>
>
> "Jacco Schalkwijk" wrote:
>

mymobile

2005-07-27, 11:23 am

Great I will try this when I get back to the office tomorrow. I just can't
sleep by this kind of problem bugging me. Will feedback you tomorrow. Thanks
a lot!

"Jacco Schalkwijk" wrote:

> Yep, just write the query in Query Analyzer and press Ctrl+I. You can
> basically click next a few times and then choose "Select all tables" and
> that's all you need for decent results. It's a good idea to run this against
> a test or development copy of your database, as it will have an impact on
> the server.
>
> There is nothing wrong with 11 million rows in a table, as long as it is
> indexed well. People work with tables with hundreds of millions of rows.
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
>
> "mymobile" < mymobile@discussions
.microsoft.com> wrote in message
> news:0288FE27-A6E2-4C84-A900- D29C217861F7@microso
ft.com...
>
>
>

mymobile

2005-07-27, 11:23 am

I will give you an overview of my table when I get back to the office.

I am doing deletes in batches around 100,000 to 250,000 records (I dont know
if this is small for 11 Million Records).

I am doing these deletes to stablize my data and avoid duplications. Since I
am still in a test environment, entry of data to my table is not yet fully
furnished.

Like for example if I insert the data of the whole month of May 2005, I
would first try to execute a delete command to delete all May 2005
transactions on my table before inserting the new May 2005 records. This is
to avoid duplication of records.

Since I really do not have an idea if my *other* test queries pefectly
inserted all the May 2005 records that is why I tried to delete any May 2005
transactions before entering a new set of records for May 2005.

"Mike Epprecht (SQL MVP)" wrote:
[color=darkred]
> Hi
>
> Do you have appropriate indexes in place?
> What does your table look like?
> Are you doing your deletes in smaller batches to help concurrency?
>
> Regards
> --------------------------------
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
>
> MVP Program: http://www.microsoft.com/mvp
>
> Blog: http://www.msmvps.com/epprecht/
>
>
>
> "mymobile" wrote:
>

Jacco Schalkwijk

2005-07-27, 11:23 am

Make sure that if you do that and you have an index on the date column, that
you use something like:

DELETE FROM your_table
WHERE your_date_column >= '20050501' AND your_date_column < '20050601'

instead of using something like:
DELETE FROM your_table
WHERE MONTH(your_date_colu
mn) = 5

The last query won't use an index on the date column even when it is there.

--
Jacco Schalkwijk
SQL Server MVP

"mymobile" < mymobile@discussions
.microsoft.com> wrote in message
news:9EC09D72-2235-4D7B-BB4A- F371D4F38B0D@microso
ft.com...[color=darkred]
>I will give you an overview of my table when I get back to the office.
>
> I am doing deletes in batches around 100,000 to 250,000 records (I dont
> know
> if this is small for 11 Million Records).
>
> I am doing these deletes to stablize my data and avoid duplications. Since
> I
> am still in a test environment, entry of data to my table is not yet fully
> furnished.
>
> Like for example if I insert the data of the whole month of May 2005, I
> would first try to execute a delete command to delete all May 2005
> transactions on my table before inserting the new May 2005 records. This
> is
> to avoid duplication of records.
>
> Since I really do not have an idea if my *other* test queries pefectly
> inserted all the May 2005 records that is why I tried to delete any May
> 2005
> transactions before entering a new set of records for May 2005.
>
> "Mike Epprecht (SQL MVP)" wrote:
>

mymobile

2005-07-28, 3:23 am

Ey!

Handling 11million records is working well now after using the indexing
wizard.
However, this is only good with the query analyzer.

Here are the SQL Statements:
1. DELETE FROM CONSOLIDATESALES_T WHERE SALESDATE_SD BETWEEN
CONVERT(DATETIME, '2005-02-18 00:00:00.000') AND CONVERT(DATETIME,
'2005-02-18 00:00:00.000')

2. SELECT COUNT(ITEMCODE_VC) AS MAXIMUMCOUNT FROM CONSOLIDATESALES_T

Upon interfacing with VB I get either of the following errors:
"Timeout Expired" or "ARITHABORT setting is invalid"

My DB Structure as requested:
'DB STRUCTURE
ITEMCODE_VC VARCHAR
SALESDATE_DT DATETIME
INVOICE_VC VARCHAR
AMOUNT_MO MONEY

If you want my VB code I can also post it in here.

thanks for the help

"Jacco Schalkwijk" wrote:

> Make sure that if you do that and you have an index on the date column, that
> you use something like:
>
> DELETE FROM your_table
> WHERE your_date_column >= '20050501' AND your_date_column < '20050601'
>
> instead of using something like:
> DELETE FROM your_table
> WHERE MONTH(your_date_colu
mn) = 5
>
> The last query won't use an index on the date column even when it is there.
>
> --
> Jacco Schalkwijk
> SQL Server MVP

mymobile

2005-07-28, 3:23 am

Just for the record, my number 2 SQL statement is just for counting the
number of records in my table.

thanks

"mymobile" wrote:

> Ey!
>
> Handling 11million records is working well now after using the indexing
> wizard.
> However, this is only good with the query analyzer.
>
> Here are the SQL Statements:
> 1. DELETE FROM CONSOLIDATESALES_T WHERE SALESDATE_SD BETWEEN
> CONVERT(DATETIME, '2005-02-18 00:00:00.000') AND CONVERT(DATETIME,
> '2005-02-18 00:00:00.000')
>
> 2. SELECT COUNT(ITEMCODE_VC) AS MAXIMUMCOUNT FROM CONSOLIDATESALES_T
>
> Upon interfacing with VB I get either of the following errors:
> "Timeout Expired" or "ARITHABORT setting is invalid"
>
> My DB Structure as requested:
> 'DB STRUCTURE
> ITEMCODE_VC VARCHAR
> SALESDATE_DT DATETIME
> INVOICE_VC VARCHAR
> AMOUNT_MO MONEY
>
> If you want my VB code I can also post it in here.
>
> thanks for the help
>
> "Jacco Schalkwijk" wrote:
>
>

Jacco Schalkwijk

2005-07-28, 7:23 am

Can you post your VB code?

--
Jacco Schalkwijk
SQL Server MVP

"mymobile" < mymobile@discussions
.microsoft.com> wrote in message
news:5BF725B2-FC15-49DE-8AFA- 59D128924CD7@microso
ft.com...[color=darkred]
> Just for the record, my number 2 SQL statement is just for counting the
> number of records in my table.
>
> thanks
>
> "mymobile" wrote:
>

mymobile

2005-07-28, 11:26 am

Hi!
As requested:

'SQL STATEMENT
1. DELETE FROM CONSOLIDATESALES_T WHERE SALESDATE_SD BETWEEN
CONVERT(DATETIME, '2005-02-18 00:00:00.000') AND CONVERT(DATETIME,
'2005-02-18 00:00:00.000')
2. SELECT COUNT(ITEMCODE_VC) AS MAXIMUMCOUNT FROM CONSOLIDATESALES_T

'SOURCE CODE
Dim objCOMMAND As New ADODB.Command
Dim objRS As New ADODB.Recordset
Dim objConn As New ADODB.Connection

Dim sSQL As String

Dim sConnString As String

'SET THE CONNECTION STRING
sConnString = "Provider=SQLOLEDB. 1;Password=sa;Persis
t Security
Info=True;User ID=sa;Initial Catalog=GoldFinger;D
ata Source=SAORINAKO"

Set objConn = New ADODB.Connection

objConn.Open sConnString

'SET THE COMMAND OBJECT PROPERTIES
Set objCOMMAND.ActiveConnection = objConn
objCOMMAND.CommandText = sSQL
objCOMMAND.CommandType = adCmdText

Set objRS = objCOMMAND.Execute

Set objRS = Nothing
Set objCOMMAND = Nothing
Set objConn = Nothing

"Jacco Schalkwijk" wrote:

> Can you post your VB code?
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
>
> "mymobile" < mymobile@discussions
.microsoft.com> wrote in message
> news:5BF725B2-FC15-49DE-8AFA- 59D128924CD7@microso
ft.com...
>
>
>

mymobile

2005-07-29, 3:23 am

Hi!
Please disregard the first code that I posted here is the final one.

Dim objCOMMAND As New ADODB.Command
Dim objConn As New ADODB.Connection

Dim sConnString As String

Dim sSQL As String

'SET THE CONNECTION STRING
sConnString = "Provider=SQLOLEDB. 1;Password=sa;Persis
t Security
Info=True;User ID=sa;Initial Catalog=GoldFinger;D
ata Source=SAORINAKO"

'SQL STATEMENT HERE
sSQL = "DELETE FROM CONSOLIDATESALES_T WHERE SALESDATE_SD BETWEEN
CONVERT(DATETIME, '" & Format(dtpStart, "yyyy-mm-dd") & " 00:00:00.000') " _
& "AND CONVERT(DATETIME, '" & Format(dtpEnd, "yyyy-mm-dd") & "
00:00:00.000')"

'objConn.ConnectionTimeout = 0
'objConn.CommandTimeout = 0

objConn.Open sConnString

Set objCOMMAND.ActiveConnection = objConn

'objCOMMAND.CommandText = "avoidduplicate"
'objCOMMAND.CommandType = adCmdStoredProc

objCOMMAND.CommandText = sSQL
objCOMMAND.CommandType = adCmdText

'objCOMMAND.Parameters.Append objCOMMAND.CreateParameter("DateFrom", adDate,
adParamInput, , dtpStart)
'objCOMMAND.Parameters.Append objCOMMAND.CreateParameter("DateTo", adDate,
adParamInput, , dtpEnd)

objCOMMAND.Execute

objConn.Close

Set objCOMMAND = Nothing
Set objConn = Nothing

'DB STRUCTURE
'ITEMCODE_VC VARCHAR - index primary, non cluster
'SALESDATE_DT DATETIME
'INVOICE_VC VARCHAR
'AMOUNT_MO MONEY

"Jacco Schalkwijk" wrote:

> Can you post your VB code?
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
>

Sponsored Links