Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Simple select query with no where or join takes a long time Expand / Collapse
Author
Message
Posted Sunday, July 7, 2013 2:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 22, 2013 4:43 PM
Points: 2, Visits: 6
Do you guys have any idea why a simple select statement without any joins or where clause would take over 9 minutes to run? The table holds under 300,000 records and isn't used for anything yet.

Table:

CREATE TABLE [dbo].[TEMP_RSS_PRODUCTS2](
[product_name] [varchar](500) NULL,
[noride_add] [varchar](70) NULL,
[manufacturer_name] [varchar](50) NULL,
[product_longdesc] [nvarchar](4000) NULL,
[pno] [varchar](100) NOT NULL,
[product_no] [int] NOT NULL,
[main_part_no] [varchar](50) NULL,
[mfr_pno] [varchar](50) NULL,
[price] [smallmoney] NULL,
[product_id] [varchar](70) NULL,
[weight] [float] NULL,
[width] [float] NULL,
[height] [float] NULL,
[depth] [float] NULL,
[est_min_shipping] [smallmoney] NULL,
[dropship_fee] [smallmoney] NULL,
[qualifies_for_free_shipping] [bit] NULL,
[status] [varchar](4) NULL,
[upc_code] [varchar](30) NULL,
[qty_available] [int] NULL,
[vehicle_specific] [bit] NULL,
[pic] [varchar](255) NULL,
[option_desc] [varchar](60) NULL,
[ship_zip] [char](5) NULL,
[keywords] [varchar](1024) NULL,
[retail_price] [smallmoney] NULL,
[main_product_name] [varchar](300) NULL,
[gender] [char](1) NULL,
[age] [char](5) NULL,
[color] [varchar](50) NULL,
[material] [varchar](50) NULL,
[viscosity] [varchar](20) NULL,
[size] [varchar](30) NULL,
[size_unit] [varchar](15) NULL,
[sale_price] [smallmoney] NULL,
[sale_end_dt] [smalldatetime] NULL,
[pattern] [varchar](50) NULL,
[num_options] [int] NULL,
[p_upc] [varchar](30) NULL,
[p_isbn] [varchar](30) NULL,
[option_id] [uniqueidentifier] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [IX_TEMP_RSS_PRODUCTS2_pno] ON [dbo].[TEMP_RSS_PRODUCTS2]
(
[pno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_TEMP_RSS_PRODUCTS2_product_no] ON [dbo].[TEMP_RSS_PRODUCTS2]
(
[product_no] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO


Query selects all columns except the last 4:

select  
product_name,
noride_add,
manufacturer_name,
product_longdesc,
pno,
product_no,
main_part_no,
mfr_pno,
price,
product_id,
[weight],
width,
height,
depth,
est_min_shipping,
dropship_fee,
qualifies_for_free_shipping,
[status],
upc_code,
qty_available,
vehicle_specific,
pic,
option_desc,
ship_zip,
keywords,
retail_price,
main_product_name,
gender,
age,
color,
material,
viscosity,
size,
size_unit,
sale_price,
sale_end_dt,
pattern
from dbo.TEMP_RSS_PRODUCTS2


Execution plan says:

SELECT Cost: 0%
Table Scan Cost: 100%

Thanks for any help.


Post #1470967
Posted Sunday, July 7, 2013 2:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 2:17 AM
Points: 56, Visits: 518
Hi!
- Check locks: run query with nolock hint, and check running time
- Check hardware performance (CPU, Disk, Memory) under query running
Post #1470968
Posted Sunday, July 7, 2013 7:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 15, 2014 1:24 PM
Points: 3, Visits: 131
There are several wide columns in your table. With 300,000 rows, that could add up to quite a bit of data. It may take a few minutes to transfer the result set.
Post #1470971
Posted Sunday, July 7, 2013 9:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 6:48 AM
Points: 125, Visits: 361
You could also index the table, it makes running very fast. Use clustered index
Post #1470975
Posted Sunday, July 7, 2013 12:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 22, 2013 4:43 PM
Points: 2, Visits: 6
Thank you all for the replies! The size was it. I didn't think it could possibly be that big, but it was about 1.5 GB, so it was just the time it took to get the result set back over the Internet. Thanks!
Post #1470981
Posted Sunday, July 7, 2013 12:48 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:31 AM
Points: 2,956, Visits: 2,977
Hi,

Your two nonclustered indexes are not used by your select * query. Use clustered index instead. You can extend your nonclustered indexes with INCLUDE(col1,col2,...coln) but you have to add a WHERE clause that will make a use of it.
You can find some articles on indexes http://www.sqlservercentral.com/search/?q=indexing&t=a
Clustered index is the best choice in your case.

Regards,
IgorMi




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1470982
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse