Poor Performance in SQL Server 2000 Database with SP3a 8.00.760

Hello All,

In our production database we have 1 Millions number of records in the database. In one database server we have install Service Pack SP3a 8.00.818 version. Performance of the system seems to be good but performance is poor in another server we have installed Service Pack SP3a 8.00.760 version. Whether this problem is due to the version miss match.

Can any DBA can help in sorting this issue?

I'm moving this thread to the SQL Server Database Engine forum.|||

You have the security patch on your first server. Details can be found here

http://support.microsoft.com/kb/821277/en-us

Pls help! Delete records in VB.net

Currently i writing a program to delete Records in the SQL database using VB.net language...
BUT i not sure whether i am right?
Pls provide me with the coding using a command object to delete records in SQL database...thank...however i tried the codes below but not working ...Pls help

Dim StrConnection As String = "workstation id=""ET-T15404-PC1"";integrated security=SSPI; etc

Dim objConnection As New SqlConnection(strConnection)
Dim strSQL As String = "Delete Seller.Admin FROMSeller WHERE Seller.no=tb.no"
Dim dbComm As New SqlCommand(strSQL, objConnection)

objConnection.Open()
dbComm.ExecuteNonQuery
objConnecion.Close()Check out this Quickstart for information on how to accomplish this:Server-Side Data Access: Deleting Data in a SQL Database

Terri

Pls Help If Statement

Hi All,
I have a query in formula.I created formula like this

IF {SALEEXP.EXPCODE}=144001966 THEN
{SALEEXP.AMOUNT}

it shows all the records from amount like this
ABDTI06000003 SMILE CARDS
24091700413 24091700413
404.42 0.00 0.00 0.00
0.00 45.33 0.00 0.00
0.00 0.00 -168.64 0.00
0.00 0.00 0.00
Pls help me to solve this problemPlease provide a sample of records you're running through this formula. Include headers to the columns of data.|||Hi Wenin,
It comes like this.

SALES Gujrat
Gujrat
Gujrat
Gujrat
Octrai Bill / Rounding Total
Vat Sale Vat Sale Vat Sale Vat Sale Cash





discount Off
12.5%
12.5%
4%
4%

Taxable Taxable Taxable Taxable
Sale Tax
Sale
Tax


ABDTI06000003 SMILE CARDS 0.00 0.00 0.00 0.00
[LST NO:24091700413][CST
NO:24091700413]

ABDTI06000003 SMILE CARDS 404.42 0.00 0.00
[LST NO:24091700413][CST
NO:24091700413]

ABDTI06000003 SMILE CARDS 0.00 -168.64 0.00
[LST NO:24091700413][CST
NO:24091700413]

ABDTI06000003 SMILE CARDS 0.00 45.33 0.00 0.00
[LST NO:24091700413][CST
NO:24091700413]|||Hi Wenin,
Pls see the attachment. It comes like this pls give me the solution|||It's not clear what the problem is.
Which one of the data items is the formula output, and what's wrong with the output? What are you expecting?

Plenty of records to choose, what type of JOIN?

I have 100k + records in table usr_table and I want to select all of them that do not have the same ID as the ID's indicated in table Usr_Type_Data

Select * From usr_table As t1
Join Company_Sales.dbo.Usr_Type_Data As t2 ON
t2.user_id = t1.user_id Where CustomerTypeId <> 7

I get 0 returned.

If I change it to:

Select * From usr_table As t1
Join Company_Sales.dbo.Usr_Type_Data As t2 ON
t2.user_id = t1.user_id Where CustomerTypeId = 7

I get all records from table Usr_Data_Type returned.

Do I need to specify a special Join type?

Or perhaps change the T-SQL around?

you can try a Left Outer Join to see if that brings back the results your looking for.

Select * From usr_table As t1
Left Outer Join Company_Sales.dbo.Usr_Type_Data As t2 ON
t2.user_id = t1.user_id Where CustomerTypeId <> 7

|||

Hi there,

Yeah I thought of that also, but it does not work.

Must be something that works?

I just thought of something, the Where clause in the above code is effectively working against both tables after they were joined correct?

Is there anywhat to put a where clause on just 1 of the tables?

|||

First of all I'd check to make sure you have data in your tables that meet your criteria.

You could try a full outer join as well. The type of join you use really is determined by how the data is stored.

|||

"I have 100k + records in table usr_table and I want to select all of them that do not have the same ID as the ID's indicated in table Usr_Type_Data"

One way is to use the NOT EXISTS clause.

Select *From usr_tableWhereNOT EXISTS (Select *From usr_type_data.UserID = usr_table.UserID)

Note that if Usr_Type_Data is also a large volume table - this could be slow. If you need to optimize for performance, then you may need a solution involving a couple of steps with a temporary table - but maybe not.

please test my script to analyze table keys (was "Submitted for your review...:)

Here is a script I wrote that analyzes datasets and returns all the minimal composite and unary keys that uniquely identify records. I wrote it because I frequently have to analyze client spreadsheets and non-normalized data tables.

On my desktop server it took about two minutes to analyze 2000 permutations of a table with 50 columns and 5000 records.

Please try it out for me and let me know if it chokes on anything, or if you see any ways it could be improved!Dude...still looking at it...

Only 2 minutes you say....

hmmmmmmmmmmmmmm

EDIT: I ran it against Nothwind And I got Nothing|||4 minutes 28 seconds on my desktop

Candidate Fields Permutations Checked
------ -------
61 1891

It only returned my identity column.|||Dooh..

It's for one table at a time...I thought you were doing an entire database

OK, I did Products in Northwind and it didn't pick ProductName as a Natural Key...

Candidate Fields Permutations Checked
10 66

Natural Keys Found
[UnitPrice], [UnitsInStock]
[SupplierID], [UnitPrice], [ReorderLevel]
[SupplierID], [UnitsInStock], [ReorderLevel]
[UnitPrice], [UnitsOnOrder], [ReorderLevel]|||Yowch!

Obviously a bug. I will look into it.

Thanks, Brett!|||It may be because there is no unique index on PoductName...but the data is all unique in the sample...

Go figure M$... I wonder if it was done on purpose to show the "Benefits" of Surrogate keys...

"An Apple is an Apple until it's renamed"|||No, its a bug in my script.

The point of the script is to find natural keys, whether or not they have been defined that way on the table. Otherwise, I'd just query sysindexes.

There is a flaw in the recursive logic which I need to track down. If I comment out a clause meant to eliminate redundant branches, the script returns the correct results, but if I don't eliminate redundant branches then I am reduced to testing every permutation, which is impractical.

The solution will probably occur to me in the car on the way home tonight.

Have a good weekend, all.|||I tried to challenge your logic by going after statistics info rather than doing "select count(distinct [fieldname])..." But got drawn into trying to come up with an algorythm:

Once you get all non-text/image columns into a temp table (I also eliminated sql_variant by doing nullif(prec, 0) is not null), instead of doing a cursor I was thinking to create permutations by doing "order by newid()" within the loop with "top @.number_of_qualifying_columns"... And of course, as usual, got distracted, never finished, etc.

Have you thought of that?|||I've tried two different methods of searching permutations. The first was "bottom up", starting with single columns and then adding from there, but required an exhaustive search.

I'm hoping that by using the "top down" approach that I posted I can identify and eliminate searching branches of permutations that are known not to contain natural keys, or that already contain a subset known to be a natural key.

The algorithm used to create the permutations is not as difficult or imporatant as the algorithm used to eliminate permutations.

I have an idea in the back of my head (which did occur to me in the car on the way home!), and I'm trying to come up with a way to implement it.

I think it is an interesting challenge which would also prove useful to solve, so I'm surprised I haven't seen it done before.

Anybody else here is welcome to take a shot at it! Just write a script that efficiently identifies all the unary and minimal composite keys in a table.

Please help: SQL Server Saving performance problem.

Dear Sir,
I have a program to write 30000 record to the database every minute
during daily operation. The program complete to save 30000 records in 30
sconds under normal sitaution. At night, all store data will be backup to
another table and truncate the table. But after running the program several
days, I find that the program need take about 3-5 minutes to save 30000
records completely even the table is empty. Actaully, all external
sitautions is unchanged such as number of user to access the table, the
amount of read data...etc. The performance is down so much. Then I need
shut down the SQK Server Service and then start it again. Then , it will
fine again.
WHy? How can I make sure that the saving can finish withthin 1 minute?
Regards,
Anthony Lam
Just a thought...
Could it be an autogrow kicking in, which leads to the load wait?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AA" <anthony@.jadeflex.com> wrote in message news:OrolLuXPEHA.1160@.TK2MSFTNGP09.phx.gbl...
> Dear Sir,
> I have a program to write 30000 record to the database every minute
> during daily operation. The program complete to save 30000 records in 30
> sconds under normal sitaution. At night, all store data will be backup to
> another table and truncate the table. But after running the program several
> days, I find that the program need take about 3-5 minutes to save 30000
> records completely even the table is empty. Actaully, all external
> sitautions is unchanged such as number of user to access the table, the
> amount of read data...etc. The performance is down so much. Then I need
> shut down the SQK Server Service and then start it again. Then , it will
> fine again.
> WHy? How can I make sure that the saving can finish withthin 1 minute?
> --
> Regards,
> Anthony Lam
>
|||yes, autogrow for the db and log without restrict.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> bl
news:uJJf6AYPEHA.272@.TK2MSFTNGP12.phx.gbl g...
> Just a thought...
> Could it be an autogrow kicking in, which leads to the load wait?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AA" <anthony@.jadeflex.com> wrote in message
news:OrolLuXPEHA.1160@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
to[vbcol=seagreen]
several[vbcol=seagreen]
need
>
|||So that could potentially be the reason. Growing a file takes time, and new inserts are blocked until the
autogrow is finished. I'd consider pre-allocating storage.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AA" <anthony@.jadeflex.com> wrote in message news:epXeBhgPEHA.1048@.tk2msftngp13.phx.gbl...
> yes, autogrow for the db and log without restrict.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> bl
> news:uJJf6AYPEHA.272@.TK2MSFTNGP12.phx.gbl g...
> news:OrolLuXPEHA.1160@.TK2MSFTNGP09.phx.gbl...
> to
> several
> need
>

Please help: SQL Server Saving performance problem.

Dear Sir,
I have a program to write 30000 record to the database every minute
during daily operation. The program complete to save 30000 records in 30
sconds under normal sitaution. At night, all store data will be backup to
another table and truncate the table. But after running the program several
days, I find that the program need take about 3-5 minutes to save 30000
records completely even the table is empty. Actaully, all external
sitautions is unchanged such as number of user to access the table, the
amount of read data...etc. The performance is down so much. Then I need
shut down the SQK Server Service and then start it again. Then , it will
fine again.
WHy? How can I make sure that the saving can finish withthin 1 minute?
--
Regards,
Anthony LamJust a thought...
Could it be an autogrow kicking in, which leads to the load wait?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AA" <anthony@.jadeflex.com> wrote in message news:OrolLuXPEHA.1160@.TK2MSFTNGP09.phx.gbl...
> Dear Sir,
> I have a program to write 30000 record to the database every minute
> during daily operation. The program complete to save 30000 records in 30
> sconds under normal sitaution. At night, all store data will be backup to
> another table and truncate the table. But after running the program several
> days, I find that the program need take about 3-5 minutes to save 30000
> records completely even the table is empty. Actaully, all external
> sitautions is unchanged such as number of user to access the table, the
> amount of read data...etc. The performance is down so much. Then I need
> shut down the SQK Server Service and then start it again. Then , it will
> fine again.
> WHy? How can I make sure that the saving can finish withthin 1 minute?
> --
> Regards,
> Anthony Lam
>|||yes, autogrow for the db and log without restrict.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ¦b¶l¥ó
news:uJJf6AYPEHA.272@.TK2MSFTNGP12.phx.gbl ¤¤¼¶¼g...
> Just a thought...
> Could it be an autogrow kicking in, which leads to the load wait?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AA" <anthony@.jadeflex.com> wrote in message
news:OrolLuXPEHA.1160@.TK2MSFTNGP09.phx.gbl...
> > Dear Sir,
> > I have a program to write 30000 record to the database every minute
> > during daily operation. The program complete to save 30000 records in 30
> > sconds under normal sitaution. At night, all store data will be backup
to
> > another table and truncate the table. But after running the program
several
> > days, I find that the program need take about 3-5 minutes to save 30000
> > records completely even the table is empty. Actaully, all external
> > sitautions is unchanged such as number of user to access the table, the
> > amount of read data...etc. The performance is down so much. Then I
need
> > shut down the SQK Server Service and then start it again. Then , it will
> > fine again.
> > WHy? How can I make sure that the saving can finish withthin 1 minute?
> >
> > --
> > Regards,
> >
> > Anthony Lam
> >
> >
>|||So that could potentially be the reason. Growing a file takes time, and new inserts are blocked until the
autogrow is finished. I'd consider pre-allocating storage.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AA" <anthony@.jadeflex.com> wrote in message news:epXeBhgPEHA.1048@.tk2msftngp13.phx.gbl...
> yes, autogrow for the db and log without restrict.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ¦b¶l¥ó
> news:uJJf6AYPEHA.272@.TK2MSFTNGP12.phx.gbl ¤¤¼¶¼g...
> > Just a thought...
> >
> > Could it be an autogrow kicking in, which leads to the load wait?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "AA" <anthony@.jadeflex.com> wrote in message
> news:OrolLuXPEHA.1160@.TK2MSFTNGP09.phx.gbl...
> > > Dear Sir,
> > > I have a program to write 30000 record to the database every minute
> > > during daily operation. The program complete to save 30000 records in 30
> > > sconds under normal sitaution. At night, all store data will be backup
> to
> > > another table and truncate the table. But after running the program
> several
> > > days, I find that the program need take about 3-5 minutes to save 30000
> > > records completely even the table is empty. Actaully, all external
> > > sitautions is unchanged such as number of user to access the table, the
> > > amount of read data...etc. The performance is down so much. Then I
> need
> > > shut down the SQK Server Service and then start it again. Then , it will
> > > fine again.
> > > WHy? How can I make sure that the saving can finish withthin 1 minute?
> > >
> > > --
> > > Regards,
> > >
> > > Anthony Lam
> > >
> > >
> >
> >
>

Please help: SQL Server Saving performance problem.

Dear Sir,
I have a program to write 30000 record to the database every minute
during daily operation. The program complete to save 30000 records in 30
sconds under normal sitaution. At night, all store data will be backup to
another table and truncate the table. But after running the program several
days, I find that the program need take about 3-5 minutes to save 30000
records completely even the table is empty. Actaully, all external
sitautions is unchanged such as number of user to access the table, the
amount of read data...etc. The performance is down so much. Then I need
shut down the SQK Server Service and then start it again. Then , it will
fine again.
WHy? How can I make sure that the saving can finish withthin 1 minute?
Regards,
Anthony LamJust a thought...
Could it be an autogrow kicking in, which leads to the load wait?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AA" <anthony@.jadeflex.com> wrote in message news:OrolLuXPEHA.1160@.TK2MSFTNGP09.phx.gbl...[v
bcol=seagreen]
> Dear Sir,
> I have a program to write 30000 record to the database every minute
> during daily operation. The program complete to save 30000 records in 30
> sconds under normal sitaution. At night, all store data will be backup to
> another table and truncate the table. But after running the program severa
l
> days, I find that the program need take about 3-5 minutes to save 30000
> records completely even the table is empty. Actaully, all external
> sitautions is unchanged such as number of user to access the table, the
> amount of read data...etc. The performance is down so much. Then I need
> shut down the SQK Server Service and then start it again. Then , it will
> fine again.
> WHy? How can I make sure that the saving can finish withthin 1 minute?
> --
> Regards,
> Anthony Lam
>[/vbcol]|||yes, autogrow for the db and log without restrict.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> bl
news:uJJf6AYPEHA.272@.TK2MSFTNGP12.phx.gbl g...
> Just a thought...
> Could it be an autogrow kicking in, which leads to the load wait?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AA" <anthony@.jadeflex.com> wrote in message
news:OrolLuXPEHA.1160@.TK2MSFTNGP09.phx.gbl...
to[vbcol=seagreen]
several[vbcol=seagreen]
need[vbcol=seagreen]
>|||So that could potentially be the reason. Growing a file takes time, and new
inserts are blocked until the
autogrow is finished. I'd consider pre-allocating storage.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AA" <anthony@.jadeflex.com> wrote in message news:epXeBhgPEHA.1048@.tk2msftngp13.phx.gbl...[v
bcol=seagreen]
> yes, autogrow for the db and log without restrict.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> bl
> news:uJJf6AYPEHA.272@.TK2MSFTNGP12.phx.gbl g...
> news:OrolLuXPEHA.1160@.TK2MSFTNGP09.phx.gbl...
> to
> several
> need
>[/vbcol]