Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-09-11 : 14:12:35
|
| Fabian Ramirez writes "I have a sql table wich primary key (clustered index) is a credit card number. In lab tests excecuting a process to check card status filling table with sequential numbers (16 digit), my process makes 50 querys by second. But when i fill the table with non sequential card numbers I only obtain at last 11 querys by second. I'm using a multithread (5 threads) delphi application using the process into a stored procedure.Could you help me.... This is the table..Name Owner Type Created_datetime -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- --------------------------- CARD dbo user table 2002-09-09 16:43:29.103 Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ACCOUNT bigint no 8 no (n/a) (n/a) NULLBALANCE1 money no 8 19 4 no (n/a) (n/a) NULLBALANCE2 money no 8 19 4 no (n/a) (n/a) NULLCASHLIMIT |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-09-12 : 06:25:35
|
| What exactly are you looking for help with? As you pointed out, your test data did not represent the distribution statistics of your live data? Did this surprise you?Jay White{0} |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-09-12 : 08:07:36
|
| Also as you have a clustered index on this field - by populating with random numbers you will end up page splitting.If your test populated in the order of the number then you will fill all pages to the max possible and so greatly reduce the number of reads.You may be able to speed thing up by rebuilding the index with a low fill factor - but you will need to do this often as any inserts/updates will then cause page splits.You might want to make it a non clustered index to speed up the rebuild.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.Edited by - nr on 09/12/2002 08:10:31 |
|
|
|
|
|
|
Indexed search with sequential numbers.