Solved

Fetching data is taking time from database

Posted on 2006-11-08
16
199 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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now