473,725 Members | 2,417 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Select query execute very slow

106 New Member
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 4607
ck9663
2,878 Recognized Expert Specialist
Where's the query?

~~ CK
Feb 12 '10 #2
Hamayun Khan
106 New Member
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 Recognized Expert Specialist
Have you tried indexing your table?

~~ CK
Feb 16 '10 #4
Hamayun Khan
106 New Member
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 Recognized Expert Top Contributor
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 Recognized Expert Top Contributor
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 New Member
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 Recognized Expert Top Contributor
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

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

Similar topics

21
5252
by: John Fabiani | last post by:
Hi, I'm a newbie and I'm attempting to learn howto create a select statement. When I use >>> string1='18 Tadlock Place' >>> cursor.execute("SELECT * FROM mytest where address = %s",string1) All works as expected. But >>> numb=10 >>> cursor.execute("SELECT * FROM mytest where clientID = %d",numb) Traceback (innermost last): File "<stdin>", line 1, in ?
2
9244
by: Benoit Le Goff | last post by:
Hello. I test some query on sql server 2000 (sp2 on OS windows 2000) and i want to know why a simple query like this : select * from Table Where Column like '%value' is more slow on 2000 than on sql 7. And this case arrive only if the % character is in the begin. If you test this : select * from Table Where Column like 'v%alue'
5
1884
by: eddie wang | last post by:
hello, I have the following query. it returns result in less than 1 second. select * from employee e, address a where e.id=a.emp_id and e.id=1234 The problem is that it becomes extremely slow if i take the last line out. So the query looks like: select *
1
2238
by: David Lawson | last post by:
The line indicated below from my php script is very slow (about 10 seconds). I have this field indexed so I thought that it would be much faster. Could someone tell me what might be wrong? I'm also including the dump of the table definitions. This is a cd cataloging database. Right now the filenames table is empty and I'm trying to populate it, but at the rate it's going it would take days. I have about 700,000 records in the 'files'...
3
4285
by: uthuras | last post by:
hi all, One of my customer having warehouse database running on DB2. Recently when they execute the following query at db2 command line the exepected result take ages before return result select count(*) from dss.fncsvcar where ar_id no in (select ar_id from dss.ar) Both tables ar_id is not a key field. However, indexes are created
0
2184
by: Florian | last post by:
Hi Uthuras, it would be helpful to see the access path. However sometimes it makes sense to rewrite a "not in" to gain better access paths: I tried the following - hopefully similar - statements in my environment. Watch the timerons of the different ways to get the same result set: Your Original:
2
4493
by: Wei Wang | last post by:
Hi, I want to do a select in dynamic command, something like: TRIGGER FUNCTION DECLARE table_name_suffix text; temp_result RECORD; temp_result2 RECORD;
5
8337
by: parwal.sandeep | last post by:
Hello grp! i'm using INNODB tables which are using frequently . if i fire a SELECT query which fetch major part of table it usually take 10-20 seconds to complete. in mean time if any UPDATE qry comes for a perticular row which is part of SELECT qry i want to know that whether UPDATE will wait for completing SELECT qry or not, or it simply executing without bothering SELECT qry .
1
4327
by: Mikle | last post by:
I need to update values in a table (where they are null or zero) with values from another table for the corresponding PopulationMemberID. Now I now that you can't update where there is a join.. but the join is in the nested select.. so it should work right? CurrentDb.Execute ("UPDATE Samples SET samples.SampleMemberEmployees=(SELECT pop.PopMemberEmployees FROM population as pop WHERE .=. ) WHERE nz(sampleMemberEmployees,0)=0 AND
3
9117
by: RAG2007 | last post by:
I'm using the QueryDef and Execute method to update a record in my MySQL backend. Problem: When the Passthrough update query is defined using QueryDef, it becomes a select query, and I cannot use the Execute Method. Instance: The following code is defined as a query called "AddCost" UPDATE tblinitiative SET tblinitiative.Estimate = " & Estimate & ", tblinitiative.FinalCost = " & FinalCost & " WHERE (((tblinitiative.InitID)=" & InitID &...
0
8749
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9398
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9250
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9097
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8079
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4776
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3216
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2626
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2153
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.