Select query execute very slow

[Hamayun Khan]

Hi.

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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblJobScrap](
    [JobPostID] [bigint] IDENTITY(1,1) NOT NULL,
    [SchoolID] [int] NULL CONSTRAINT [DF__tblJobScr__Schoo__0E04126B]  DEFAULT (NULL),
    [DomainID] [int] NULL,
    [MemType] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [asap] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [region] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_tblJobScrap_region]  DEFAULT (N' '),
    [LEA] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_tblJobScrap_LEA]  DEFAULT (N' '),
    [Cat1IDs] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_tblJobScrap_SchoolPhase]  DEFAULT (''),
    [Cat2IDs] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_tblJobScrap_Cat2IDs]  DEFAULT (''),
    [Cat3IDs] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_tblJobScrap_Cat3IDs]  DEFAULT (''),
    [JobTitle] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Contract] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Hoursabl] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [County] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [StartDate] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ClosingDate] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [PayScale] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [JobDesc] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [JobDesc1] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [JobDesc2] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [JobDesc3] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [PostDate] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [NumViewed] [int] NULL CONSTRAINT [DF__tblJobScr__NumVi__0EF836A4]  DEFAULT ((0)),
    [LastUpdate] [datetime] NULL,
    [extlink] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [institutionName] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [web] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [logoimage] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [SSMA_TimeStamp] [timestamp] NOT NULL,
    [scraped] [bit] NULL CONSTRAINT [DF_tblJobScrap_scraped]  DEFAULT ((1)),
    [Optimized] [bit] NULL CONSTRAINT [DF_tblJobScrap_Optimized]  DEFAULT ((0)),
    [GroupID] [int] NULL,
 CONSTRAINT [PK_tblJobScrap] PRIMARY KEY CLUSTERED 
(
    [JobPostID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
 
GO
ALTER TABLE [dbo].[tblJobScrap]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$tblJobScrap$extlink$disallow_zero_length] CHECK  ((len([extlink])>(0)))
GO
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.

[ck9663]

Where's the query?

~~ CK

[Hamayun Khan]

Thanks for reply

This is the query

SELECT [JobPostID], [region], [LEA], [Cat1IDs], [Cat2IDs], [Cat3IDs], [JobTitle], [Contract], [PayScale], [JobDesc], [PostDate], [NumViewed] FROM [tblJobScrap]

and on aspx page the code is as below

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="JobPostID"
    DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display.">
    <columns>
<asp:BoundField DataField="region" SortExpression="region" HeaderText="region"></asp:BoundField>
<asp:BoundField DataField="LEA" SortExpression="LEA" HeaderText="LEA"></asp:BoundField>
<asp:BoundField DataField="Cat1IDs" SortExpression="Cat1IDs" HeaderText="Cat1IDs"></asp:BoundField>
<asp:BoundField DataField="Cat2IDs" SortExpression="Cat2IDs" HeaderText="Cat2IDs"></asp:BoundField>
<asp:BoundField DataField="Cat3IDs" SortExpression="Cat3IDs" HeaderText="Cat3IDs"></asp:BoundField>
<asp:BoundField DataField="JobTitle" SortExpression="JobTitle" HeaderText="JobTitle"></asp:BoundField>
<asp:BoundField DataField="Contract" SortExpression="Contract" HeaderText="Contract"></asp:BoundField>
<asp:BoundField DataField="PayScale" SortExpression="PayScale" HeaderText="PayScale"></asp:BoundField>
<asp:BoundField DataField="JobDesc" SortExpression="JobDesc" HeaderText="JobDesc"></asp:BoundField>
<asp:BoundField DataField="NumViewed" SortExpression="NumViewed" HeaderText="NumViewed"></asp:BoundField>
</columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:dbase_DataConnectionString6 %>"    
    ProviderName="<%$ ConnectionStrings:dbase_DataConnectionString6.ProviderName %>"
    SelectCommand="SELECT [JobPostID], [region], [LEA], [Cat1IDs], [Cat2IDs], [Cat3IDs], [JobTitle], [Contract], [PayScale], [JobDesc], [PostDate], [NumViewed] FROM [tblJobScrap]" >
</asp:SqlDataSource>

[ck9663]

Have you tried indexing your table?

~~ CK

[Hamayun Khan]

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

[Delerna]

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

SELECT Top 50 [JobPostID], [region], [LEA], [Cat1IDs], [Cat2IDs], [Cat3IDs], [JobTitle], [Contract], [PayScale], [JobDesc], [PostDate], [NumViewed] FROM [tblJobScrap]
 

that sould indicate whether
its the query that is slow
or
its the page rendering that is slow

[Delerna]

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)

<html>
<head>
   <script language=vbscript>
      Set cnnSQL=CreateObject("ADODB.Connection")
      cnnSQL.Open "PROVIDER=SQLOLEDB;DATA SOURCE=SqlDataSource1;User ID=youruid;Password=yourpwrd;Database=yourdatabase;"      
      set rst = CreateObject("ADODB.Recordset")  
      strSQL="SELECT [JobPostID], [region], [LEA], [Cat1IDs], [Cat2IDs], [Cat3IDs], [JobTitle], [Contract], [PayScale], [JobDesc], [PostDate], [NumViewed] FROM [tblJobScrap]"      
      rst.Open strSQL,cnnSQL   
      rst.close
      set rst=nothing
      cnnSQL.close
      set cnnSQL=nothing
      msgbox "Done"
   </script>   
</head>
<body >   
</body>
</html>
 

if the msgbox pops up quickly then it is not the query slowing things down

[Hamayun Khan]

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.

[Delerna]

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

 
SelectCommand="SELECT [JobPostID], [region], [LEA], [Cat1IDs], [Cat2IDs], [Cat3IDs], [JobTitle], [Contract], [PayScale], [JobDesc], [PostDate], [NumViewed] " _
& " FROM [tblJobScrap] " _
& " WHERE [PostDate] >= '2010-01-01' and [PostDate] <='2010-01-31'" 
 
 

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