July 7, 2013 at 2:23 am
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,
[varchar](50) NULL,
[material] [varchar](50) NULL,
[viscosity] [varchar](20) NULL,
[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.
July 7, 2013 at 2:47 am
Hi!
- Check locks: run query with nolock hint, and check running time
- Check hardware performance (CPU, Disk, Memory) under query running
July 7, 2013 at 7:20 am
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.
July 7, 2013 at 9:08 am
You could also index the table, it makes running very fast. Use clustered index
July 7, 2013 at 12:45 pm
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!
July 7, 2013 at 12:48 pm
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,My blog: www.igormicev.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply