SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Simple select query with no where or join takes a long time


Simple select query with no where or join takes a long time

Author
Message
yeezer1
yeezer1
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 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.
salliven
salliven
Mr or Mrs. 500
Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)

Group: General Forum Members
Points: 546 Visits: 678
Hi!
- Check locks: run query with nolock hint, and check running time
- Check hardware performance (CPU, Disk, Memory) under query running
WBinKC
WBinKC
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 195
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.
timotech
timotech
Mr or Mrs. 500
Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)

Group: General Forum Members
Points: 525 Visits: 461
You could also index the table, it makes running very fast. Use clustered index
yeezer1
yeezer1
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 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!
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10396 Visits: 5157
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search