Re: Sql Server response very slow

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Re: Sql Server response very slow

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2009-06-26 : 05:16:25

Hallo,

I have an access database which connects to a Sql server 2005 via an ODBC to extract data from tables. The problem is one of the table's is built with 100,000,000 records. Therefore, just to extract 7 fields for one record it takes 20 minutes, which is really so inconvenient.
Please could anyone advise me what to do so that to stimulate a quick extract.

Things to consider;

- the space available on the drive where sql server is planted is around 1.8 GB.. Is space an issue?
- the database in sql server has only two tables and the recovery mode has been sent to simple.. N/B - the database is static and was created ONLY to store the two tables, the data on these tables will not be updated nor deleted.

Any ideas to improve the performnace?

Thank you very much

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-26 : 05:27:56

Sounds like an index issue.

What index(s) do you have on the table? With a decent compound index you should have no problems getting information from a table of that size.

Is the dable a heap? (i.e does it have a primary key?)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2009-06-26 : 05:36:24

Thanks for the reply!!

The table has no primary key and about the index where exactly do I look.. Sorry am new in sql

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2009-06-26 : 05:39:39

Ok in the Table designer

Indexable is set to YES

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-26 : 05:44:50

Can you post the table structure?
Can you also post the code for a typical query against this table?

E 12°55'05.63"
N 56°04'39.26"

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2009-06-26 : 05:56:08

o_pat_uid decimal(20, 0) Checked
o_prac_uid int Checked
o_col_uid int Checked
o_oper_uid decimal(20, 0) Checked
o_pat_id nvarchar(4) Checked
o_pat_eid decimal(20, 0) Checked
o_pat_birth_yr decimal(4, 0) Checked
o_pat_birth_mth decimal(2, 0) Checked
o_pat_birth_pla nvarchar(22) Checked
o_pat_fst_ac_dt datetime Checked
o_pat_fst_reg_dt datetime Checked
o_pat_vm_id nvarchar(12) Checked
o_pat_curr_gender nvarchar(13) Checked
o_pat_curr_marstat nvarchar(13) Checked
o_pat_curr_smoke nvarchar(13) Checked
o_pat_curr_drink nvarchar(13) Checked
o_pat_curr_height nvarchar(12) Checked
o_pat_curr_weight nvarchar(12) Checked
o_pat_curr_bmi nvarchar(12) Checked
o_pat_curr_reg_sta int Checked
o_pat_curr_trodt datetime Checked
o_pat_curr_tror int Checked
o_pat_curr_chsregf nvarchar(1) Checked
o_pat_family_no decimal(6, 0) Checked
o_pat_bld_grp_uid int Checked
o_pat_death_date_i datetime Checked
o_pat_gprd_start_d datetime Checked
o_pat_gprd_end datetime Checked
o_pat_mother_uid_i decimal(20, 0) Checked
o_pat_father_uid_i decimal(20, 0) Checked
o_pat_reg_f nvarchar(1) Checked
o_pat_death_dt_i_f nvarchar(6) Checked
o_pat_death_date datetime Checked
o_pat_mothr_calc_f nvarchar(6) Checked
o_father_calc_f nvarchar(6) Checked
o_pat_ev_rec_f nvarchar(1) Checked
o_pat_reg_det_f nvarchar(1) Checked
o_pat_val_sex_f nvarchar(1) Checked
o_pat_patage_f nvarchar(1) Checked
o_pat_acc_f nvarchar(1) Checked
o_pat_sys_d datetime Checked
o_pat_crt_d datetime Checked
o_pat_his_d datetime Checked
o_pat_del_d datetime Checked
o_pat_dwload nvarchar(6) Checked

Checked means Allow NULLs

Typical query;

Select o_pat.o_pat_id, o_pat.o_pat_eid, o_pat.o_pat_birth_pla
From o_pat
Where (o_pat.o_pat_eid = ‘9168’)

This will take edges to bring the results regardless o_pat_eid = 9168 is the first record

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-26 : 06:14:39

Start by creating a nonclustered index like this.

CREATE NONCLUSTERED INDEX IX_Pat ON {Your Table Name Here}
(o_pat_eid) INCLUDE (o_pat_id, o_pat_birth_pla)

then re-execute your query again.

E 12°55'05.63"
N 56°04'39.26"

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2009-06-26 : 06:26:28

Tried but it is very very slow executing the query...

Anything to do with table "o_pat" is extremely slow

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-26 : 06:35:57

Creating the index can be very slow.
Is also (after creating the index) the query slow?

If so, drop the nonclustered index and create this clustered index.

CREATE CLUSTERED INDEX IX_Pat ON {Your Table Name Here}
(o_pat_eid, o_pat_id, o_pat_birth_pla)

E 12°55'05.63"
N 56°04'39.26"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-26 : 07:19:29

Creating the clustered index will probably be slow! The index is a b-tree that has to catalogue the physical structure of the pages in your table. The benefit of the index that peso is giving you is that it is a covering index which should be able to be referenced by your sql query.

What's happening right now is that, without any kind of index to check, the query must scan all the rows in your table (100's of millions) to find the data you want.

Hopefully, with a nice index then the query will be able to navigate the b-tree very quickly to isolate the row(s) you require.

Here's some reference reading:
http://msdn.microsoft.com/en-us/library/ms177443(SQL.90).aspx

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources