Solved

Fetching data is taking time from database

Posted on 2006-11-08
16
201 Views
Last Modified: 2008-02-01
Hi,

I have around 400,000 records in the database. I am using MS SQL 2005

I have created indexes for the required fields in all the tables to which I am joining.

SQL take lot of time to fetch the data sometime around 1.20 min

Can anyone help me that what should I do to retieve data fast.

Kind Regards,
Dinesh
0
Comment
Question by:dinesh_bali
  • 6
  • 4
  • 2
  • +4
16 Comments
 
LVL 23

Expert Comment

by:adathelad
ID: 17896847
Hi,

Can you post your query?
0
 
LVL 1

Expert Comment

by:Yogeshup
ID: 17896985
Hi,

The time taken would depend on the following
1. The hardware you are using (processor, RAM, hard disc, etc)
2. The parameters set for SQL Server
3. The manner in which the query is written
4. The size of the data being queried
5. The size of the output data being fetched
4. The manner in which the output is retrieved
5. Other factors ;-)

I undersand that you have only 400,000 rows in the entire database and you also have the necessary indexes. That should rule out the first 2 points. But how many rows do you have in each of the tables being queried? and what is the size of the output? And where are you executing the query from? Also, can you check the execution plan in the query analyser and see the operation that is taking the maximum resources (seen as % of the total resources taken) and if the query is using the necessary indexes? Also, in some cases, adding hints can help substansially. I can help you out if you can paste the query along with the execution plan in XML.



0
 
LVL 11

Expert Comment

by:regbes
ID: 17897011
Hi dinesh_bali,

post the query , table structures and exsisting indexes so we can optimise your query/indexes

HTH

R.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:dinesh_bali
ID: 17897034
Hi,

Thanks for your reply.

Below is my computer configuration

I am using Pentium 4
CPU 3.00 GHz
504 MB of RAM

Also please tell me how to check

1. The parameters set for SQL Server? Also what it should be.
2. Execution plan in XML

Currently I am trying to fetch only one record for specific email

SELECT CUS.CTCT_ID, CUS.CTCT_CREATIONDATE, CUS.CTCT_LASTACCESSDATE,
            CUS.CTCT_TITLE, CUS.CTCT_FIRSTNAME, CUS.CTCT_LASTNAME, CUS.CTCT_PASSWORD,
            CUS.CTCT_ADDRESSLINE1, CUS.CTCT_ADDRESSLINE2, CUS.CTCT_ADDRESSLINE3,
            CUS.CTCT_BUILDINGNUMBER, CUS.CTCT_CITY, CUS.CTCT_POSTCODE, CUS.CTCT_STATE_COUNTY,
            CUS.COCU_ID, CUS.CTCT_EMAIL, CUS.CTCT_NATIONALITY, CUS.CTPL_ID, DOP.DCOP_CULTURE ,DOP.DCOP_ID,
            DOP.DCOP_MARKETINGCONTACTEMAIL, DOP.DCOP_MARKETINGCONTACTEPOSTAL,
            DOP.DCOP_MARKETINGCONTACTETHIRDPARTY,CNY.COCU_CODE, PFL.CTPL_CODE
FROM CT_CUSTOMER CUS
            INNER JOIN DC_DISTRIBUTIONCHANNELOPTIONS DOP ON DOP.CTCT_ID = CUS.CTCT_ID
            INNER JOIN DC_DISTRIBUTIONCHANNEL DCH ON DCH.DCDC_ID = DOP.DCDC_ID
            INNER JOIN DC_DISTRIBUTIONCHANNELTYPE DTY ON DTY.DCDT_TYPE = DCH.DCDT_TYPE
            INNER JOIN CO_COUNTRY CNY ON CNY.COCU_ID = CUS.COCU_ID            
            LEFT OUTER JOIN CT_PREFERREDLANGUAGE PFL ON CUS.CTPL_ID = PFL.CTPL_ID
WHERE
            DTY.DCDT_DESCRIPTION = 'abc'
            AND CUS.CTCT_EMAIL = 'my email'

Thanks again

Kind Regards,
Dinesh
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17897039
how many records does thisquery return?
0
 
LVL 11

Expert Comment

by:regbes
ID: 17897064
dinesh_bali,

First make sure all the below tables have clusterd indexes on them

DC_DISTRIBUTIONCHANNELOPTIONS
CT_CUSTOMER
DC_DISTRIBUTIONCHANNEL
DC_DISTRIBUTIONCHANNELTYPE
CO_COUNTRY
CT_PREFERREDLANGUAGE

If the below fields are not indexed create indexes on them

DC_DISTRIBUTIONCHANNELOPTIONS.CTCT_ID
DC_DISTRIBUTIONCHANNELOPTIONS.DCDC_ID

CT_CUSTOMER.CTCT_ID
CT_CUSTOMER.CTPL_ID
CT_CUSTOMER.COCU_ID

DC_DISTRIBUTIONCHANNEL.DCDC_ID
DC_DISTRIBUTIONCHANNEL.DCDT_TYPE

DC_DISTRIBUTIONCHANNELTYPE.DCDT_TYPE

CO_COUNTRY.COCU_ID

CT_PREFERREDLANGUAGE.CTPL_ID
0
 

Author Comment

by:dinesh_bali
ID: 17897153
Hi,

Thanks for your prompt reply and the way you are helping

My this query returns only one record

I have created the indexes in the following way:

CREATE INDEX CTCT_ID_IND

    ON CT_CUSTOMER (CTCT_ID)

GO

CREATE INDEX DOP_CTCT_ID_IND

    ON DC_DISTRIBUTIONCHANNELOPTIONS (CTCT_ID)

GO

CREATE INDEX DCDC_ID_ID_IND

    ON DC_DISTRIBUTIONCHANNELOPTIONS (DCDC_ID)
   
    GO

CREATE INDEX DCOP_ID_IND

    ON DC_DISTRIBUTIONCHANNELOPTIONS (DCOP_ID)
   
    GO

Can you please help me how to creating clustured indexes and also tell me how to check if this exist or not

Kind regards,
Dinesh
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17897257
>>Can you please help me how to creating clustured indexes and also tell me how to check if this exist or not

to create clustered index you have to add clustered keyword which creating the the index

for example
CREATE clustered INDEX DCOP_ID_IND
    ON DC_DISTRIBUTIONCHANNELOPTIONS (DCOP_ID)
      GO

to check if index exists or not
you have to check
sysindexes table. or simply in query analyzer highlight yourtable and prss <alt>+<f1>
0
 
LVL 11

Expert Comment

by:regbes
ID: 17897466
dinesh_bali,

run the following for each table

sp_helpindex TableName

this will tell you all the indexes on a table and their type
0
 

Author Comment

by:dinesh_bali
ID: 17898286
Hi, I have checked all the indexes and everything seems fine.

Still the query has given me the result in 55 sec, Query has fetched only one record in 55 sec.

Also, For the above mentioned tables on the primary key

There was already clustered indexes made on primary key before and I have made another non- clustered index for their primary key.

Will it make any difference? I mean clustered and non -clustered index for same field?

Pls. give me some more suggestions to help me to make my result faster?

Kind Regards,
Dinesh
0
 
LVL 28

Accepted Solution

by:
imran_fast earned 250 total points
ID: 17898306
run sql tuning advisor and check your query for index recommendation.
0
 
LVL 11

Expert Comment

by:regbes
ID: 17898338
dinesh_bali,

what one of these tables has the most data?

DC_DISTRIBUTIONCHANNELOPTIONS
CT_CUSTOMER
DC_DISTRIBUTIONCHANNEL
DC_DISTRIBUTIONCHANNELTYPE
CO_COUNTRY
CT_PREFERREDLANGUAGE
0
 

Author Comment

by:dinesh_bali
ID: 17898573
Thanks for all you are trying to help me

Here is the details you asked:

select count(*) from CT_CUSTOMER // Total Count 363469
select count(*) from DC_DISTRIBUTIONCHANNELOPTIONS // Total Count 364498
select count(*) from DC_DISTRIBUTIONCHANNEL // Total Count 10
select count(*) from DC_DISTRIBUTIONCHANNELTYPE // Total Count 2
select count(*) from CO_COUNTRY // Total Count 40
select count(*) from CT_PREFERREDLANGUAGE // Total Count 24


First time when I run a query it takes around 1 min to get the result. but if I run it again and again then the results comes fast.

I want the result to come fast from the first attempt.

Kind Regards,
Dinesh
0
 

Author Comment

by:dinesh_bali
ID: 17898851
I tried to run Tunning Advisor?

I get the following index recommendation:


use [ENET_LIVE_NEW]
go

CREATE NONCLUSTERED INDEX [_dta_index_CT_CUSTOMER_8_1386540073__K25_K1_K15_K29_2_3_4_5_6_7_8_9_10_11_12_13_14_27] ON [dbo].[CT_CUSTOMER]
(
      [CTCT_EMAIL] ASC,
      [CTCT_ID] ASC,
      [COCU_ID] ASC,
      [CTPL_ID] ASC
)
INCLUDE ( [CTCT_CREATIONDATE],
[CTCT_LASTACCESSDATE],
[CTCT_TITLE],
[CTCT_FIRSTNAME],
[CTCT_LASTNAME],
[CTCT_PASSWORD],
[CTCT_ADDRESSLINE1],
[CTCT_ADDRESSLINE2],
[CTCT_ADDRESSLINE3],
[CTCT_BUILDINGNUMBER],
[CTCT_CITY],
[CTCT_POSTCODE],
[CTCT_STATE_COUNTY],
[CTCT_NATIONALITY]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE STATISTICS [_dta_stat_1386540073_15_1_29] ON [dbo].[CT_CUSTOMER]([COCU_ID], [CTCT_ID], [CTPL_ID])
go

CREATE STATISTICS [_dta_stat_1386540073_25_29_15] ON [dbo].[CT_CUSTOMER]([CTCT_EMAIL], [CTPL_ID], [COCU_ID])
go

CREATE STATISTICS [_dta_stat_1386540073_25_15_1_29] ON [dbo].[CT_CUSTOMER]([CTCT_EMAIL], [COCU_ID], [CTCT_ID], [CTPL_ID])
go

CREATE NONCLUSTERED INDEX [_dta_index_DC_DISTRIBUTIONCHANNELOPTIONS_8_1595920807__K3_K1_K2_4_5_6_7] ON [dbo].[DC_DISTRIBUTIONCHANNELOPTIONS]
(
      [CTCT_ID] ASC,
      [DCOP_ID] ASC,
      [DCDC_ID] ASC
)
INCLUDE ( [DCOP_CULTURE],
[DCOP_MARKETINGCONTACTEMAIL],
[DCOP_MARKETINGCONTACTEPOSTAL],
[DCOP_MARKETINGCONTACTETHIRDPARTY]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE STATISTICS [_dta_stat_1595920807_1_3] ON [dbo].[DC_DISTRIBUTIONCHANNELOPTIONS]([DCOP_ID], [CTCT_ID])
go

CREATE STATISTICS [_dta_stat_1595920807_2_1_3] ON [dbo].[DC_DISTRIBUTIONCHANNELOPTIONS]([DCDC_ID], [DCOP_ID], [CTCT_ID])
go



NOW WHAT I SHOULD DO?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 17899721
Add *ONE* index, as follows:

CREATE INDEX CTCT_EMAIL_IND
    ON CT_CUSTOMER (CTCT_EMAIL)
0
 

Author Comment

by:dinesh_bali
ID: 17900050

My problem is solved because of running sql tuning advisor and checking my query for index recommendation as guided by imran_fast

Thanks all of you, who has tried to help me.

Kind Regards,
Dinesh


0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Get the latest status 8 30
Help Required 3 93
SSRS Enable Remote Errors 4 24
performance query 4 20
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question