By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,179 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

Select query execute very slow

100+
P: 106
Hi.

I have a table with more than 30000 records having below structure.

Expand|Select|Wrap|Line Numbers
  1. SET ANSI_NULLS ON
  2. GO
  3. SET QUOTED_IDENTIFIER ON
  4. GO
  5. CREATE TABLE [dbo].[tblJobScrap](
  6.     [JobPostID] [bigint] IDENTITY(1,1) NOT NULL,
  7.     [SchoolID] [int] NULL CONSTRAINT [DF__tblJobScr__Schoo__0E04126B]  DEFAULT (NULL),
  8.     [DomainID] [int] NULL,
  9.     [MemType] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  10.     [asap] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  11.     [region] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_tblJobScrap_region]  DEFAULT (N' '),
  12.     [LEA] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_tblJobScrap_LEA]  DEFAULT (N' '),
  13.     [Cat1IDs] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_tblJobScrap_SchoolPhase]  DEFAULT (''),
  14.     [Cat2IDs] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_tblJobScrap_Cat2IDs]  DEFAULT (''),
  15.     [Cat3IDs] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_tblJobScrap_Cat3IDs]  DEFAULT (''),
  16.     [JobTitle] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  17.     [Contract] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  18.     [Hoursabl] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  19.     [County] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  20.     [StartDate] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  21.     [ClosingDate] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  22.     [PayScale] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  23.     [JobDesc] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  24.     [JobDesc1] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  25.     [JobDesc2] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  26.     [JobDesc3] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  27.     [PostDate] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  28.     [NumViewed] [int] NULL CONSTRAINT [DF__tblJobScr__NumVi__0EF836A4]  DEFAULT ((0)),
  29.     [LastUpdate] [datetime] NULL,
  30.     [extlink] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  31.     [institutionName] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  32.     [web] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  33.     [logoimage] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  34.     [SSMA_TimeStamp] [timestamp] NOT NULL,
  35.     [scraped] [bit] NULL CONSTRAINT [DF_tblJobScrap_scraped]  DEFAULT ((1)),
  36.     [Optimized] [bit] NULL CONSTRAINT [DF_tblJobScrap_Optimized]  DEFAULT ((0)),
  37.     [GroupID] [int] NULL,
  38.  CONSTRAINT [PK_tblJobScrap] PRIMARY KEY CLUSTERED 
  39. (
  40.     [JobPostID] ASC
  41. )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
  42. ) ON [PRIMARY]
  43.  
  44. GO
  45. ALTER TABLE [dbo].[tblJobScrap]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$tblJobScrap$extlink$disallow_zero_length] CHECK  ((len([extlink])>(0)))
  46. GO
  47. ALTER TABLE [dbo].[tblJobScrap] CHECK CONSTRAINT [SSMA_CC$tblJobScrap$extlink$disallow_zero_length]
I have asp.net page having select query that select all records from the table. the asp.net page shows 10 records per page. The page loads very slow, almost in min 30 seconds. I want to speed up the page load to 5 to 10 seconds. I don't know how i will do this.


Any Help in this regard. Thanks in advance.
Feb 12 '10 #1
Share this Question
Share on Google+
8 Replies


ck9663
Expert 2.5K+
P: 2,878
Where's the query?

~~ CK
Feb 12 '10 #2

100+
P: 106
Thanks for reply

This is the query

Expand|Select|Wrap|Line Numbers
  1. SELECT [JobPostID], [region], [LEA], [Cat1IDs], [Cat2IDs], [Cat3IDs], [JobTitle], [Contract], [PayScale], [JobDesc], [PostDate], [NumViewed] FROM [tblJobScrap]
and on aspx page the code is as below

Expand|Select|Wrap|Line Numbers
  1. <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="JobPostID"
  2.     DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display.">
  3.     <columns>
  4. <asp:BoundField DataField="region" SortExpression="region" HeaderText="region"></asp:BoundField>
  5. <asp:BoundField DataField="LEA" SortExpression="LEA" HeaderText="LEA"></asp:BoundField>
  6. <asp:BoundField DataField="Cat1IDs" SortExpression="Cat1IDs" HeaderText="Cat1IDs"></asp:BoundField>
  7. <asp:BoundField DataField="Cat2IDs" SortExpression="Cat2IDs" HeaderText="Cat2IDs"></asp:BoundField>
  8. <asp:BoundField DataField="Cat3IDs" SortExpression="Cat3IDs" HeaderText="Cat3IDs"></asp:BoundField>
  9. <asp:BoundField DataField="JobTitle" SortExpression="JobTitle" HeaderText="JobTitle"></asp:BoundField>
  10. <asp:BoundField DataField="Contract" SortExpression="Contract" HeaderText="Contract"></asp:BoundField>
  11. <asp:BoundField DataField="PayScale" SortExpression="PayScale" HeaderText="PayScale"></asp:BoundField>
  12. <asp:BoundField DataField="JobDesc" SortExpression="JobDesc" HeaderText="JobDesc"></asp:BoundField>
  13. <asp:BoundField DataField="NumViewed" SortExpression="NumViewed" HeaderText="NumViewed"></asp:BoundField>
  14. </columns>
  15. </asp:GridView>
  16. <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:dbase_DataConnectionString6 %>"    
  17.     ProviderName="<%$ ConnectionStrings:dbase_DataConnectionString6.ProviderName %>"
  18.     SelectCommand="SELECT [JobPostID], [region], [LEA], [Cat1IDs], [Cat2IDs], [Cat3IDs], [JobTitle], [Contract], [PayScale], [JobDesc], [PostDate], [NumViewed] FROM [tblJobScrap]" >
  19. </asp:SqlDataSource>
Feb 13 '10 #3

ck9663
Expert 2.5K+
P: 2,878
Have you tried indexing your table?

~~ CK
Feb 16 '10 #4

100+
P: 106
Thanks for reply.

Hi I am an sql beginer. I don't know how indexing is applied to table. Please help me how i can apply indexing to the above table.

regards
Feb 18 '10 #5

Delerna
Expert 100+
P: 1,134
I think its probably not the query that is slow.
You have no where clause in your query and your not joining any tables so an index won't really help.

Displaying 30,000 records in a web page is going to take a long time to render.
At least, from my experience it will.

try this
Expand|Select|Wrap|Line Numbers
  1. SELECT Top 50 [JobPostID], [region], [LEA], [Cat1IDs], [Cat2IDs], [Cat3IDs], [JobTitle], [Contract], [PayScale], [JobDesc], [PostDate], [NumViewed] FROM [tblJobScrap]
  2.  
that sould indicate whether
its the query that is slow
or
its the page rendering that is slow
Feb 18 '10 #6

Delerna
Expert 100+
P: 1,134
another way to test the speed of the query by itself would be to create an ordinary html document
and paste this code into it (you will of course need to provide the appropriate connection details)
Expand|Select|Wrap|Line Numbers
  1. <html>
  2. <head>
  3.    <script language=vbscript>
  4.       Set cnnSQL=CreateObject("ADODB.Connection")
  5.       cnnSQL.Open "PROVIDER=SQLOLEDB;DATA SOURCE=SqlDataSource1;User ID=youruid;Password=yourpwrd;Database=yourdatabase;"      
  6.       set rst = CreateObject("ADODB.Recordset")  
  7.       strSQL="SELECT [JobPostID], [region], [LEA], [Cat1IDs], [Cat2IDs], [Cat3IDs], [JobTitle], [Contract], [PayScale], [JobDesc], [PostDate], [NumViewed] FROM [tblJobScrap]"      
  8.       rst.Open strSQL,cnnSQL   
  9.       rst.close
  10.       set rst=nothing
  11.       cnnSQL.close
  12.       set cnnSQL=nothing
  13.       msgbox "Done"
  14.    </script>   
  15. </head>
  16. <body >   
  17. </body>
  18. </html>
  19.  
if the msgbox pops up quickly then it is not the query slowing things down
Feb 18 '10 #7

100+
P: 106
Hi. Thanks for your help. If my site loads slow because of the number of records i select from table (which is, I m sure) then is there is any way to minimize time sqlserver return the data.
Apr 5 '10 #8

Delerna
Expert 100+
P: 1,134
Design the page/pages to work with less data.
Date ranges are good for that, perhaps [Post Date]
You might want to put an index on that field

your query then becomes something like
Expand|Select|Wrap|Line Numbers
  1.  
  2. SelectCommand="SELECT [JobPostID], [region], [LEA], [Cat1IDs], [Cat2IDs], [Cat3IDs], [JobTitle], [Contract], [PayScale], [JobDesc], [PostDate], [NumViewed] " _
  3. & " FROM [tblJobScrap] " _
  4. & " WHERE [PostDate] >= '2010-01-01' and [PostDate] <='2010-01-31'" 
  5.  
  6.  
That way your query is only pulling data from the table 1 month at a time
You will need to provide on the page a way for your user to select the month of interrest
Apr 6 '10 #9

Post your reply

Sign in to post your reply or Sign up for a free account.