SQL Server Performance: Query Tuning vs. Process Tuning



Record Set Size

The selection speed of different record sets is not linear to the number of rows. Because many steps have to be taken for any selections, getting extra records out of the database often hardly takes any more time. In a typical database, I have about 17 million records in a table. By making a selection of 20,000, 50,000, 100,000, and 150,000 records, I calculated the execution time per record. These are some of the results:

Rows

Rows / Second

20,000

476

51,987

456

20,000

377

51,987

702

50,000

704

133,276

1,293

50,000

694

133,276

1,211

100,000

1,369

282,818

2,643

100,000

1,388

282,818

2,525

150,000

2,027

421,581

3,798

150,000

2,027

421,581

3,603

20,000

408

51,987

577

20,000

400

51,987

742

50,000

735

133,276

1,402

50,000

735

133,276

1,373

100,000

1,449

282,818

2,525

100,000

1,470

282,818

2,459

150,000

2173

421,581

4,093

150,000

2,142

421,581

4,053

This test indicates that one selection of 100,000 records is about three times as fast as four selections of 20,000 records each. So if possible, get all the information you need in one selection instead of going back to the database many times.

Continues…

Pages: 1 2 3 4




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |