Order By Is Slowing Page Load
Mar 14, 2007
hi guys,
the following test script works fine and displays a list of cars from the fairly small database, but if I specify the sort order in the querystring, the page takes ages to display and usually times out. Can someone look over it please and tell me where I can fine-tune it for performance or redundant code?
thanks
M
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
Dim oRS,oConn,myOrder,strSQL
Set oRS = Server.CreateObject("ADODB.Recordset")
Set oConn = Server.CreateObject("ADODB.Connection")
'next, a couple of test lines to prevent timeout (seems to have no effect)
oConn.CommandTimeout = 0
Server.ScriptTimeout = 0
Set strOrder = Request.QueryString("Order")
oConn.ConnectionString = "Provider=MSDASQL;DRIVER=SQL Server;SERVER=address;UID=userID;PWD=password;DATA BASE=name"
oConn.Open
%>
</head>
<body>
<%
strSQL = "Select make,model,price from vehicles where cat = 'car' AND active = 'yes'"
if strOrder <> "" then
strSQL = strSQL & " ORDER BY " & strOrder
end if
oRS.Open strSQL, oConn, 2, 3
oRS.moveFirst
Do while not oRS.eof
make = oRS("make")
model = oRS("model")
price = oRS("price")
%>
<%=make%> <%=model%> <%=price%><BR>
<%
oRS.MoveNext
loop
oRS.close
set oRS= nothing
oConn.close
set oConn=nothing
%>
</body>
</html>
View 14 Replies
ADVERTISEMENT
Jan 7, 2007
Finding the "pieces of information" I need to successfully install the SQL Server Express edition is so complex. Uninstalls do "not" really uninstall completely, leading to failure of SQL install. Can you suggest a thorough, one-stop site for directions for the order of app uninstalls and then the order for app installs for the following...
SQL Server Express edition
Visual Studios 2005
Jet 4.0 newest upgrade
.Net Framework 2.0 (or should I use 3.0)
VS2005 Security upgrade
Anything else I need for just creating a database for my VS2005 Visual Basic project?
I was trying to use MS Access as my backend db but would like to try SQL Express
Thank you, Mark
View 7 Replies
View Related
Jul 23, 2005
The data file is a simple Unicode file with lines of text. BCPapparently doesn't guarantee this ordering, and neither does theimport tool. I want to be able to load the data either sequentially oradd line numbering to large Unicode file (1 million lines). I don'twant to deal with another programming language if possible and Iwonder if there's a trick in SQL Server to get this accomplished.Thanks for any help.Mark Leary----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups---= East/West-Coast Server Farms - Total Privacy via Encryption =---
View 15 Replies
View Related
Nov 23, 2004
Does anyone have a script that analyzes primary and foreign key relationships of tables and produces a suggested load order based upon dependancies?
Thanks,
Fred.
View 4 Replies
View Related
Aug 29, 2006
I use SQLDataSource, bind to a GridView and a SelectParameter that gets it's value from a textbox. If the textbox is empty, then it returns all rows.How do I prevent the query and binding when the page first load? Thanks
View 11 Replies
View Related
Nov 15, 2006
I have one aspx page that is very slow loading it is connected to SQL 2005 database. I am looking for any preformance tips out there
View 3 Replies
View Related
Feb 25, 2008
Hi,in aspx I've SqlDataSource, in SelectParametersI can add ControlParameter and to load parametersfrom control Property, BUT I'm trying to do that:Is there way, when in <ControlParameter>to set parameter to be loaded from PAGE Property.something like that:aspx:<asp:SqlDataSource ID="_companyDS" runat="server" ConnectionString="<%$ ConnectionStrings:tihomir_dbConnectionString %>" SelectCommand="SELECT [companyID], [companyName], [companyInfo], [companyAddress] FROM [Companies] WHERE ([companyID] = @companyID)"> <SelectParameters> <asp:ControlParameter ControlID="Page" PropertyName="CompanyIdent" Name="companyID" Type="Int32" DefaultValue="0" /> </SelectParameters> </asp:SqlDataSource></asp:SqlDataSource>code behind:public partial class test : System.Web.UI.Page{ public int CompanyIdent { get { return ... some id ...; } }} Best Regards,Tihomir Ivanov Best Manager Software
View 2 Replies
View Related
Jul 25, 2006
Hi all,
I have a webpage with a Datagrid that populates using a table adapter from a Stored procedure that exists in my SQL Database...If I run the Stored procedure in SQL Directly then it takes 20 Secs to return all records...If I run the webpage then it takes just over 20 Secs..
Great you say..But If I have the sorting option set in ASP.net and I click on a column to sort then off the page goes for another 20 secs to sort the data..
Is there a better way to do what I am doing here that will speed up the page load..
Ie..the data is returned once and then sorted...
Is it Better / Quicker for me to create a table using the stored procedure and link to this from the website..Updating the table every couple of minutes ?
Any advice please ?
Ray..
View 4 Replies
View Related
Feb 21, 2008
I want to insert client browser and computer information upon page load. I want to insert strComputerName into @ComputerName & strIpAddress into @IpAddress
what would the insert look like under the page load?
SqlDataSource1.Insert() ? ? ? ? ?
aspx
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CompInfoConnectionString1 %>"
SelectCommand="SELECT * FROM [Computer_Info]" InsertCommand="INSERT INTO [Computer_Info] ([ComputerName], [IpAddress], [BrowserInfo], [LastLogin], [UserName]) VALUES (@ComputerName, @IpAddress, @BrowserInfo, @LastLogin, @UserName)"
code behine aspx.vbProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'pull comp nameDim host As System.Net.IPHostEntry
Dim strComputerName As String
Dim strIpAddress As Stringhost = System.Net.Dns.GetHostByAddress(Request.ServerVariables.Item("REMOTE_HOST"))
strComputerName = host.HostName
CompNameLabel.Text = strComputerName
'pull ip address
strIpAdress = Request.ServerVariables.Item("REMOTE_HOST")IpAddressLabel.Text = strIpAddress
End Sub
View 1 Replies
View Related
Apr 2, 2007
Since you cannot use dataset fields in page headers is there any other way to dynamically load and image?
View 1 Replies
View Related
Apr 10, 2014
I have files which has date in file name and I want to load all files in sequential order.
Here I am giving you an example of my file name.
"NetworkActivity_869_403722_01-01-2014.log"
"NetworkActivity_869_403722_01-02-2014.log"
"NetworkActivity_869_403722_01-03-2014.log"
"NetworkActivity_869_403722_01-04-2014.log"
"NetworkActivity_869_403722_01-05-2014.log"
These are my files in that I want to load files sequentially means jan 1st, 2nd like this way.
View 5 Replies
View Related
Sep 6, 2007
lol, you still still didn't answer the most important questions about what you want the results to be, and whether the data is consistent.
View 1 Replies
View Related
Jan 24, 2005
yeah, you're right, it isn't totally clear: Quote: MS SQL Development forum discussing administration, MS SQL queries, and other MS SQL-related topics. i suppose "MS SQL" could be interpreted as "any product from microsoft that uses the sql language" but unfortunately what it really means is "microsoft sql server"
anyhow...
please show your latest query that doesn't work
View 8 Replies
View Related
Feb 14, 2006
I am getting the following error in the database..
B-tree chain linkage mismatch
Keys out of order on page
I have run the DBCC Repair_rebuild with data_loss.
But this has not helped to resolve the issue. It is finding errors in the Primary Key Linkage.
Please assist.
Thanks for your help.
Karthik
View 20 Replies
View Related
Aug 8, 2007
Background€¦
We€™re receiving the data as flat files, when I open up the file in notepad the characters look like the error€™d examples (see below). Open the file up in a texteditor where you can apply code page 1252 it display the characters correctly, which is expected behaviour!
I use SSIS to load these files into my database, applying the correct code page€¦
€¦however the data gets loaded in the incorrect form. Plus the database is collation €˜SQL_Latin1_General_CP1_CI_AS€™ which is code page 1252 so I can€™t see why I am getting these issues.
This is an example of the Umlaut (German chars) issues were getting, they€™re appearing in the database like this€¦.
tuid travelproductid lastname
1741869 6 P€?hls
1792950 6 Sch€žfer
2087798 6 L€?schhorn
2089010 6 Sagemller
2314086 6 Jnger
€¦& should be€¦
1741869 6 Pöhls
1792950 6 Schäfer
2087798 6 Löschhorn
2089010 6 Sagemüller
2314086 6 Jünger
I think it€™s a bug with SSIS, I know having a Unicode file would certainly solve this but I don't have that option.
View 9 Replies
View Related
Apr 15, 2015
We are running SQL Server 2014 Enterprise Edition (64-Bit) on Windows 2012 R2 Standard (64-Bit).
1. When to create indexes, before or after data is added? Please address Clustered and Non-Clustered Indexes.
2. To move indexes to it's own filegroup, is it best to create the NON-Clustered Indexes on the separate filegroup with code similar to the example below?
CREATE NONCLUSTERED INDEX IX_Employee_OrganizationLevel_OrganizationNode
ON HumanResources.Employee (OrganizationLevel, OrganizationNode)
WITH (DROP_EXISTING = ON)
ON TransactionsFG1;
GO
I have read the following links that states that if you create the Clustered Index on a separate filegroup, it would also move the base table to that particular filegroup. (So I take it that you ONLY can move NON-CLustered Indexes to a separate filegroup.)
Placing Indexes on Filegroups:
[URL]
By default, indexes are stored in the same filegroup as the base table on which the index is created. A nonpartitioned clustered index and the base table always reside in the same filegroup. However, you can do the following:
• Create nonclustered indexes on a filegroup other than the filegroup of the base table.
Move an Existing Index to a Different Filegroup:
[URL]
Limitations and Restrictions
• If a table has a clustered index, moving the clustered index to a new filegroup moves the table to that filegroup.
• You cannot move indexes created using a UNIQUE or PRIMARY KEY constraint using Management Studio. To move these indexes use the CREATE INDEX statement with the (DROP_EXISTING=ON) option in Transact-SQL.
View 1 Replies
View Related
Feb 7, 2008
Hi,
I'm trying to look for an efficient way to select records from two tables, combine them (not just one set above the other) and also efficiently page the results as well as dynamically order by specific columns. So far I have this....
DECLARE @Temp TABLE
(
IDINTNOT NULL,
NameNVARCHAR(128)NOT NULL,
TypeCHAR(1)NOT NULL
)
INSERT INTO @Temp
SELECT i.ID, i.Name, Type = 'I' FROM Item i
UNION
SELECT p.ID, p.Name, Type = 'P'FROM Package p
SELECT * FROM @Temp
ORDER BY Name ASC
I was going to then implement some sort of of ROW_NUMBER like paging and ordering on the @Temp table variable. Problem is there could be potentially 1000's or more Items and Packages and they would all go in this single Temp table before having records 1 to 10 returned. Is there a more efficient way of doing this before I proceed any further?
Many thanks for any help! :)
View 10 Replies
View Related
May 2, 2008
Hi,
I'm still quite a beginner with the Report Designer and now I have faced a problem. My goal is to make a reports refresh each time it is loaded. I know that this can be done by using rs:clearsession = true-method but in order this to work, it should be added to each link. There are a lots of links in the reports we are using and therefore it would be more practical to find another way to refresh the report on page load. Is there any way to do this with some kind of code attached to each report? I'm using Visual Studio 2003 and my Reporting services 2000.
View 4 Replies
View Related
Jun 3, 2003
hello,
i am getting a problem
suddenly the DB in Sql2000 has slowed down drastically
and when i have checked the logs the error is
Supersocket info:[spn register]:error 1355
pls. help me
pavan
View 2 Replies
View Related
Dec 4, 2006
I'm running into an issue when developing a clr project. I open the project to make changes and apparently it hoses up the sql server that I'm connecting to. I'm opening the CLR Project from a mapped drive on another computer. I can't figure out why just opening up the project causes the slow down. Any ideas.
Thanks,
Josh
View 1 Replies
View Related
May 29, 2001
We have a process here where a ~45Gb is copied from DB1 to DB2 (both on the same server) using the Export Data wizard and choosing the "Transfer objects and data between SQL Server 7.0 databases" - All the defaults are left as is.
Under 6.5 this method took around 24 - 26 hours, now with SQL 7.0 (SP3) it takes about 16 hours. That's great execpt towards the end of the copy it seems to be hogging most of the server resources - although the processors aren't mazed out.
Users of the other databases are having queries take 5+ mins when they normally come back in < 5 seconds. As soon as the copy finishes there query time return to 'normal'.
Anyone any ideas on where to start looking or how to analyse this problem.
Thanks in advance.
Rob Elmes
View 2 Replies
View Related
Jul 20, 2005
I've been doing some experiments with speeding up copying tables ofapproximately 1 million rows between databases using BCP and BULK INSERT.I noticed that the total time for removing the indexes (non-clustered) andthen recreating them after the BULK INSERT was significantly less than justdoing the BULK INSERT with the indexes left there, even though I specifiedTABLOCK.I would have expected SQL Server not to update the index until the insertcompleted (given the table lock) and so removing the indexes would have noeffect. Can anyone explain why removing the indexes should speed it up?This is on SQL Server 7.CheersDave
View 2 Replies
View Related
Oct 2, 2007
We're having problems with our SQL 2000 SP3 standard server (on Win 2k3). Our quasi-data-warehouse application does data processing on feeds of approximately 7 million records. Once the data is loaded in the queries against that data and updates against large tables will often wait with PAGEIOLATCH_SH contention. To give an example, over 7 million rows I was testing something out in development and issued: UPDATE <Table> SET <VarcharColumn> = null, which took forever (over an hour) with the PAGEIOLATCH problem....meanwhile someone else using another database was completely blocked from making an update during that time. It seems like something is very wrong.
The server has 4 drives spinning at 15k rpm with some sort of a high end raid controller, so I'm sure it's not a slow i/o subsystem.
Has anyone experienced this behavior before? Is this an issue that's resolved in 2000 SP4?
Thanks in advance,
-Mark
View 3 Replies
View Related
Jan 22, 2008
Hello there,
I have a SQL Server 2000 with a dozen of databases. The databases are rather small (all sum up to 10 GB).
The entire server gets extremelly slow from time to time (lasting a few days when it happens and suddently coming back to normality). A profiler trace doesn't show anything strange (besides a lot of SQL Agent entries).
I pretty much tried to isolate every single application that makes use of the databases in that server and see if it was the cause of the problem, but I couldn't find any correlation.
I know the computer where this server runs is quite fragmented. Is there any way I could make sure this is the cause of my performance issues?
I don't know if this might help, but once the server simply went down for some 3 hours, and I wasn't able to bring it up in any way. It eventually started working again by itself. The only thing I did in the meantime was to run DBCC CHECKDB a few times, always getting the response "No errors found on the database".
Any hint on that?
View 3 Replies
View Related
Apr 10, 2007
Background
SQL Server 2005 Standard 9.0.1399 64bit
Windows 2003 64-bit
8gb RAM
RAID-1 70gb HD 15K SCSI (Log Files, OS)
RAID-10 1.08TB HD 10K SCSI (Data Files)
Runs aproximately _Total 800 Transaction/Second
We deliver aproximately 70-80 million ad views / day
8 Clustered Windows 2003 32-bit OS IIS Servers running Asp.net 2.0 websites
All 8 servers talking to the one SQL server via a private network (server backbone).
In SQL Server Profiler, I see the following SQL statements with durations of 2000 - 7000:
select top 1 keywordID, keyword, hits, photo, feed from dbo.XXXX where hits > 0 order by hits
and
UPDATE XXXX SET hits=1906342 WHERE keywordID = 7;
Where the hits number is incremented by one each time that is selcted for that keyword ID.
Sometimes these happen so frequently the server stops accepting new connectinos, and I have to restart the SQL server or reboot.
Any ideas on why this is happening?
Regards,
Joe
View 6 Replies
View Related
Jan 15, 2008
Hello:
Interestingly enough, I haven't come across this before. I have a SQL stored procedure which takes four parameters; periodstartdate (datetime), periodenddate (end time), hsgradyearstart (int), hsgradyearend (int)
[dbo].[CalculateActivityTotal]
-- Add the parameters for the stored procedure here
@periodstartdate datetime = '2007-01-01',
@periodenddate datetime = '2007-01-08',
@hsgradyearstart int = 1900,
@hsgradyearend int = 2007
AS...
If I run the stored procedure and pass the parameters using EXEC or
sp_executesql "CalculateActivityTotal '2008-01-04 12:00:00', '2008-01-11 12:00:00', 1900, 2008"
the stored proc takes well over ten minutes to run (it does a bunch of aggregation). If I modify the stored procedure to take no parameters, however, and I hardcode the dates in the stored proc using declare and set then it runs in 13 seconds. What could be causing my problem and how I can I go about resolving this? I need to pass the parameters via reporting server. Thanks!
View 2 Replies
View Related
Jan 24, 2008
The server being used is a Intel Xeon E5310 Clovertown 1.6GHz 2 x 4MB L2 Cache Socket 771 80W Quad-Core 2U Passive Processor.
The problem is that this server is slowing down everytime about 1000 users log into a forum which the server is running. I think that the server should be able to handle this many users with no problems but I am not sure if that is the case.. The problem is probably something to do with the SQL of the server I am guessing. The server is not mine but I want to help the owner of the server as well as the users who are trying to access this forum but cant because of this server issue. If I was able to get the SQL would I be able to fix this problem? I doubt you need this but the server url is www.smashboards.com
I am fairly new to servers and have never really set one up myself yet. Forgive me for my lack of knowledge about them.
-Thank you!
View 1 Replies
View Related
Nov 11, 2015
I have created one reports but all the records are displaying on one page.find a solution to display the records page by page. I created the same report without group so the records are displaying in page by page.
View 3 Replies
View Related
Dec 14, 2007
Hi,
Here I will describe my problem.
1. We are loading large amount of data from database on background thread which is starting on Application_start event in global.aspx.cs file.The data is later cached for subsquent request to improve the performance.
2. Now when we put the application on web farm garden, it is not able to load the application.
3. We are sending the request the servers through Router kind of application.
4 This application is working fine on single server enviornment.
Please help us.
Ajay Kumar Dwivedi
View 1 Replies
View Related
Apr 27, 2007
I just have done the SSIS example in the tutorial document included when install SQL 2005 ENT. I have a problem that whenever I test to run, the service load all data from source with out noticing about the data (I mean it load all the data to the destination), I do it several time and it continue to load all without checking. That mean the data is dublicated when the schedule run???
I think there should be a paramete or something like that to help the engine just load the new data to the destination. Could you help please?
Thank
View 3 Replies
View Related
Aug 14, 2006
Hi everybody,
I have a query slower in SQL Server 2005 than in SQL Server 2000. I have a database in SQL 2000, I put it on the same server, but with SQL 2005 and the query take 5 seconds instead of 0 seconds. The DB compatibility is SQL Server 2000 (I tried with 2005 and result is the same). Execution plan seems right and I tried to change some DB options without results. It is weird, when I remove left join on MandatsEx, it take 2 seconds. The view currentEmployeeLevelHistoric returns 45 000 rows and mandatsex has 0 rows.
Here is the sample:
Select ls.EmployeNiveau.pk_EmployeNiveauID as NoNiveau,
IsNull(nullif(ls.Traduction.Description, ''), ls.TraductionDefaut.Description) + ' (' +
ls.currentLevelHIstoric.NoNiveau + ')' As Nom,
ls.currentEmployeeLevelHistoric.DebAssign As DateAssignationDebut,
ls.EmployeNiveau.assignmentReason As AssignmentReason,
ls.currentEmployeeLevelHistoric.FinAssign As DateAssignationFin,
ls.MandatsEx.noDossier, ls.MandatsEx.pk_MandatID,
'' As Period, ls.currentEmployeeLevelHistoric.NiveauPrincipal,
ls.currentEmployeeLevelHistoric.pk_emplNiveauHisto_Id,
ls.EmployeNiveau.fk_NiveauID_Niveaux, ls.EmployeNiveau.No_Ent_leg,
IsNull(nullif(TradPere.Description, ''), TradDefautPere.Description) + ' (' + NiveauPere.NoNiveau
+ ')' As NomSup,
ls.EmployeNiveau.No_Ent_leg + ls.EmployeNiveau.no_divisio + ls.EmployeNiveau.no_sec_eco +
ls.EmployeNiveau.no_etabli + ls.EmployeNiveau.no_mat as employeeScope,
case when ls.mandatExternalisation.fk_mandatID_MandatsEx is null then 2 else 1 end as
ContractCategory
From ls.currentEmployeeLevelHistoric
Inner Join ls.EmployeNiveau on
ls.currentEmployeeLevelHistoric.pk_EmployeNiveauID = ls.EmployeNiveau.pk_EmployeNiveauID
Inner join ls.currentLevelHistoric On
ls.EmployeNiveau.fk_NiveauID_Niveaux = ls.currentLevelHistoric.fk_niveauID_niveaux
Left Outer Join ls.TraductionDefaut On
ls.currentLevelHIstoric.fk_TraductionID_TraductionDefaut = ls.TraductionDefaut.pk_NoTraduction
Left Outer Join ls.Traduction On
ls.TraductionDefaut.pk_NoTraduction = ls.Traduction.No_Traduction and
ls.Traduction.Langue = 1
Left Join ls.MandatsEx on
ls.EmployeNiveau.fk_MandatID_MandatsEx = ls.MandatsEx.pk_MandatID
Left Join ls.mandatExternalisation on
ls.MandatsEx.pk_MandatID = ls.mandatExternalisation.fk_mandatID_MandatsEx
left Join ls.Niveaux as NiveauPere on
NiveauPere.niveauID = ls.currentLevelHIstoric.supId
Left Outer Join ls.TraductionDefaut As TradDefautPere On
NiveauPere.fk_TraductionID_TraductionDefaut = TradDefautPere.pk_NoTraduction
Left Outer Join ls.Traduction As TradPere On
TradDefautPere.pk_NoTraduction = TradPere.No_Traduction and
TradPere.Langue = 1
Where ls.EmployeNiveau.fk_EmploID_Emplos = 345158 and (convert(varchar,
ls.currentEmployeeLevelHistoric.DebAssign, 112) <= '20060802' and
(ls.currentEmployeeLevelHistoric.FinAssign is null or
convert(varchar, ls.currentEmployeeLevelHistoric.FinAssign, 112) >= '20060802'))
Thank you
Steve Gadoury
View 13 Replies
View Related
Nov 28, 2006
Hello I have a project that uses a large number of MS Data access pages created in Access 2003 and runs on MS SQL2005.
When I am on lets say my client, (first page in a series) data access page and I have completed the fields in the (DAP), I am directing my users to the next step of the registration process by means of a hyperlink to another Data access page in the same web but in a linked or sometimes different table.
I need to pass data entered /created on the first page to the next page and populate the next page with some data from the first page / table. (like staying on the client name and ID when i go to the next page)
I also need the first data access page to open and display a blank or new record. Not an existing record. I will also be looking to creata a drop down box as a record selector.
Any pointers in the right direction would be appreciated.
I am some what new to data access pages so a walk through would be nice but anything you got is welcome. Thanks Peter€¦
View 2 Replies
View Related
Sep 24, 2012
In SQL sERVER 2008, I have two fields - Depatment and Employees. I need to sort the result set by employee number ascending order, with following exception
1)when department number = 50 - the preferred order is Employee # - 573 followed by 551-572 (employee # belong to Dept 50 = 551-573)
2)When Department number = 20 – the preferred sort order is Employee # 213-220, followed by Employee # 201-213 (employee # belong to Dept 20 = 201-220)
How shall I achieve this?
View 4 Replies
View Related
