469,159 Members | 1,453 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,159 developers. It's quick & easy.

Select query execute very slow

106 100+
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
8 4322
ck9663
2,878 Expert 2GB
Where's the query?

~~ CK
Feb 12 '10 #2
Hamayun Khan
106 100+
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
2,878 Expert 2GB
Have you tried indexing your table?

~~ CK
Feb 16 '10 #4
Hamayun Khan
106 100+
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
1,134 Expert 1GB
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
1,134 Expert 1GB
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
Hamayun Khan
106 100+
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
1,134 Expert 1GB
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.

Similar topics

21 posts views Thread by John Fabiani | last post: by
2 posts views Thread by Benoit Le Goff | last post: by
5 posts views Thread by eddie wang | last post: by
1 post views Thread by David Lawson | last post: by
3 posts views Thread by uthuras | last post: by
reply views Thread by Florian | last post: by
5 posts views Thread by parwal.sandeep | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.