Fine Tune SQL Stored Procedures

[marydup]

I'm having trouble with the following stored procedure and others like it, running to slow. It is especially slow the first time into the database.
Each of the tables contains about 1,200,000 rows.


SELECT c.client_lname,
c.client_fname,
c.client_mname,
c.client_DOB,
c.client_soc_sec_num,
c.client_telephone_num,
c.client_nmidno,
c.client_sex_code,
c.client_prefix,
c.client_suffix,
c.client_type,
c.client_name_hna_format,
l.clientloc_nmidno,
l.clientloc_address_1,
l.clientloc_address_2,
l.clientloc_address_3,
l.clientloc_address_4,
l.clientloc_address_5,
l.clientloc_city,
l.clientloc_state,
l.clientloc_zip

FROM client c inner join client_location l
ON c.client_nmidno = l.clientloc_nmidno
WHERE (clientloc_state like @state)


Without and index on clientloc_state it runs about 3 minutes.
With an index on clientloc_state it runs about 1.45 minutes.
Somewhat faster but not the response time I'm looking for.
Is there a way to make this run faster? Why does response time improve to about 16 seconds the second time running the stored procedure?

Thanks

 

[JayKusch]

I believe the slowness may be somewhat attributed to the use of the LIKE conditional.

Thanks

J. Kusch

 

[tektipdjango]

The like statement is fast if the statement is
like 'X%' but not if it is '%X%' because in this case the index is not used.

But the main problem is the database design.
There is no reason to divide the client file in 2 tables.
The performance will be much higher if the table include for a client all the fields

c.client_nmidno,
c.client_lname,
c.client_fname,
...
c.client_name_hna_format,
c.clientloc_address_1,
c.clientloc_address_2,
...
l.clientloc_zip

The relational database model implies to separate informations to avoid duplicate entries and coherence. This is not the case here.

The proof is that the 2 tables are of the same size

One option would be to separate
-zip, city, state

Another to have a FamilyAdress table where all the client of the same family living in the same adress will have a reference to the same location, but I do'nt know if it's a valuable statement for your app



django
bug exterminator
tips'n tricks addict