ASP.NET

SQL query taking long time to execution RSS

5 replies

Last post Mar 01, 2012 12:57 AM by subhash.sheladia

‹ Previous Thread|Next Thread ›

- Reply
subhash.shel...

Contributor

2137 Points

487 Posts

SQL query taking long time to execution

Feb 23, 2012 02:28 PM|LINK

Hi,

I have below Store procedure, it's searching record from stkStonesDet(3 lakhs records), mstLists(Master Data Only, mostly 200 records in this table) table.

it's taking 40 sec. to execute sql, i want to search fast, please anyone help me out in this sql.

i have not mention all the fields in this query...

SELECT
       stkStones.ID,
       stkStonesDet.DiamondSKU,mstShape.Name AS ShapeName,
        mstShape.SortOrder AS ShapeSortOrder,

FROM stkStones
       INNER JOIN stkStonesDet on stkStones.ID = stkStonesDet.IDstkStones
       LEFT OUTER JOIN mstLists as mstShape on mstShape.ID = stkStonesDet.IDmstListsShape
       LEFT OUTER JOIN mstLists as mstClarity on mstClarity.ID = stkStonesDet.IDmstListsClarity
       LEFT OUTER JOIN mstLists as mstColor on mstColor.ID = stkStonesDet.IDmstListsColor
       LEFT OUTER JOIN mstLists as mstCuletCondition on mstCuletCondition.ID = stkStonesDet.IDmstListsCuletCondition
       LEFT OUTER JOIN mstLists as mstCuletSize on mstCuletSize.ID = stkStonesDet.IDmstListsCuletSize

Thanks

Subhash

Thanks
Subhash

Please, Mark as Answer if this reply helped you.
- Edited by subhash.sheladia on Feb 23, 2012 02:28 PM
- Edited by subhash.sheladia on Feb 23, 2012 02:34 PM
- Edited by subhash.sheladia on Feb 23, 2012 02:34 PM
- Edited by subhash.sheladia on Feb 23, 2012 02:34 PM
- Edited by subhash.sheladia on Feb 23, 2012 02:34 PM
- Edited by subhash.sheladia on Feb 23, 2012 02:34 PM
- Edited by subhash.sheladia on Feb 24, 2012 02:24 AM
- Edited by subhash.sheladia on Feb 24, 2012 02:24 AM
- Edited by subhash.sheladia on Feb 24, 2012 02:24 AM
- Reply
bbcompent1

All-Star

32974 Points

8500 Posts

Moderator

Re: SQL query taking long time to execution

Feb 23, 2012 03:25 PM|LINK

For that few number of records, the result should be returned quick unless you have no index defined on your table, the dataset is massive, or if your table has millions of rows.

Mark as answer posts that helped you.
- Edited by bbcompent1 on Feb 24, 2012 02:24 AM
- Reply
ryanbesko

Contributor

3551 Points

619 Posts

Re: SQL query taking long time to execution

Feb 23, 2012 04:04 PM|LINK

Put indexes on these columns:

IDstkStones

IDmstListsShape

IDmstListsClarity

IDmstListsColor

IDmstListsCuletCondition

IDmstListsCuletSize

Reporting by definition is different. Otherwise we would just show it on the screen.
- Edited by ryanbesko on Feb 24, 2012 02:24 AM
- Reply
subhash.shel...

Contributor

2137 Points

487 Posts

Re: SQL query taking long time to execution

Feb 24, 2012 04:59 AM|LINK

Hi,

Thanks for reply...

did indexes on that column even doesn't make any diff.

Thanks
Subhash

Please, Mark as Answer if this reply helped you.
- Reply
Chen Yu - MS...

All-Star

21569 Points

2493 Posts

Microsoft
Re: SQL query taking long time to execution

Feb 28, 2012 08:58 AM|LINK
subhash.sheladia

SELECT
       stkStones.ID,
       stkStonesDet.DiamondSKU,mstShape.Name AS ShapeName,
        mstShape.SortOrder AS ShapeSortOrder,

FROM stkStones
       INNER JOIN stkStonesDet on stkStones.ID = stkStonesDet.IDstkStones
       LEFT OUTER JOIN mstLists as mstShape on mstShape.ID = stkStonesDet.IDmstListsShape
       LEFT OUTER JOIN mstLists as mstClarity on mstClarity.ID = stkStonesDet.IDmstListsClarity
       LEFT OUTER JOIN mstLists as mstColor on mstColor.ID = stkStonesDet.IDmstListsColor
       LEFT OUTER JOIN mstLists as mstCuletCondition on mstCuletCondition.ID = stkStonesDet.IDmstListsCuletCondition
       LEFT OUTER JOIN mstLists as mstCuletSize on mstCuletSize.ID = stkStonesDet.IDmstListsCuletSize

Hi,

From your description, they left join same table, mstLists . You could change to AND instead of LEFT JOIN. Please check below query:
```
SELECT
        stkStones.ID,
        stkStonesDet.DiamondSKU,mstShape.Name AS ShapeName,
        mstShape.SortOrder AS ShapeSortOrder,

FROM stkStones
        INNER JOIN stkStonesDet on stkStones.ID = stkStonesDet.IDstkStones
        LEFT OUTER JOIN mstLists as mstShape on mstShape.ID = stkStonesDet.IDmstListsShape
        AND mstShape.ID = stkStonesDet.IDmstListsClarity
        AND mstShape.ID = stkStonesDet.IDmstListsColor
        AND mstShape.ID = stkStonesDet.IDmstListsCuletCondition
        AND mstShape.ID = stkStonesDet.IDmstListsCuletSize
```
Thanks.
Please mark the replies as answers if they help or unmark if not.
Feedback to us

Develop and promote your apps in Windows Store
- Reply
subhash.shel...

Contributor

2137 Points

487 Posts
Re: SQL query taking long time to execution

Mar 01, 2012 12:57 AM|LINK
Hi,

it will not work dude....it will display null values of join tables. i have tried but it display null values of
```
ShapeName, sortorder, clarity etc....

thanks
```
Thanks
Subhash

Please, Mark as Answer if this reply helped you.