SQL Slow On Godaddy, Any Optimization Fix?
Apr 8, 2008
I've transfered a site recently from reinvent
hosting to godaddy, the site works, but the pages that retrieve ms sql
(2005) queries now load about 5-8 seconds longer than they did on
reinvent (which was basically instantly)... the guy who i talked to at
godaddy said that i need to change the links on the site from external
linking to internal linking... as far as i understand it all the links
are linking internally (not using somedomain.com/page.asp?var=param....
but using ?var=param, or page2.asp?var=param), unless there's a
different use of the term that i'm not familiar with... anyway i'm
confused what he meant by it... and the asp seems to be structured ok
(and as mentioned the sql did work perfectly fine on another hosting)..
it connects to the db, retrieves the data and puts it into the array,
then loops the array for printing to page... then closes the array/db..
i think that should work perfectly fine as it did on the other
hosting... but does anyone out there know a solution to fix this on
godaddy hosting, they say it's nothing with their sql server as they
checked it and it's fine... here's the site: pccti.com ... and the
slowness is when retrieving the healthcare/IT courses and then also
displaying course details... please help
View 1 Replies
ADVERTISEMENT
Mar 1, 2005
I have a big table A_newHistory (more than 2 million rows) with primary key fund_id + date_price . This table has to be updated every 2 hours from XML.
Every row in XML must be inserted or updated (if current id and date already exist in the table) in the A_newHistory.
The following procedure works but very slow...
How can I optimize that?
================================================== =======
CREATE PROCEDURE spSaveFundsAdjustedClose
@XML ntext
AS
DECLARE @fund_id int
DECLARE @date_price datetime
DECLARE @adj_closed float
DECLARE @XMLDoc int
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
EXEC sp_xml_preparedocument @XMLDoc OUTPUT, @XML
DECLARE MutualFunds_Cursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT *
FROM OPENXML (@XMLDoc , '/xml/a', 1)
WITH ([id] INT,[date] datetime, price float)
OPEN MutualFunds_Cursor
FETCH NEXT FROM MutualFunds_Cursor
INTO @fund_id, @date_price, @adj_closed
WHILE @@FETCH_STATUS = 0
BEGIN
IF EXISTS (SELECT * FROM A_newHistory
WHERE id_fund = @fund_id AND date_price = @date_price)
BEGIN
UPDATE A_newHistory
SET adj_close = @adj_closed
WHERE id_fund = @fund_id AND date_price = @date_price
END
ELSE
BEGIN
INSERT INTO A_newHistory
VALUES(@fund_id, @date_price, @adj_closed)
END
IF @@Error <> 0
BEGIN
ROLLBACK TRANSACTION
SELECT -1
RETURN
END
FETCH NEXT FROM MutualFunds_Cursor
INTO @fund_id, @date_price, @adj_closed
END
EXEC sp_xml_removedocument @XMLDoc
CLOSE MutualFunds_Cursor
DEALLOCATE MutualFunds_Cursor
COMMIT TRANSACTION
SELECT 0
GO
================================================== =======
View 1 Replies
View Related
Apr 10, 2008
Hello Everybody,
I have a small tricky problem here...need help of all you experts.
Let me explain in detail. I have three tables
1. Emp Table: Columns-> EMPID and DeptID
2. Dept Table: Columns-> DeptName and DeptID
3. Team table : Columns -> Date, EmpID1, EmpID2, DeptNo.
There is a stored procedure which runs every day, and for "EVERY" deptID that exists in the dept table, selects two employee from emp table and puts them in the team table. Now assuming that there are several thousands of departments in the dept table, the amount of data entered in Team table is tremendous every day.
If I continue to run the stored proc for 1 month, the team table will have lots of rows in it and I have to retain all the records.
The real problem is when I want to retrive data for a employee(empid1 or empid2) from Team table and view the related details like date, deptno and empid1 or empid2 from emp table.
HOw do we optimise the data retrieval and storage for the table Team. I cannot use partitions as I have SQL server 2005 standard edition.
Please help me to optimize the query and data retrieval time from Team table.
Thanks,
Ganesh
View 4 Replies
View Related
Nov 18, 2005
I was wondering if anyone successfully managed to connect to the
asp.net 2.0 features (specifically authentication) with goDaddy. They
allow you to set up the schema for 2.0 in the SQL server, but I have
yet to manage to actually CONNECT to the SQL server using VS.NET 2005.
Anyone have any suggestions? Experiences?
View 1 Replies
View Related
Feb 12, 2006
CREATE TABLE [Sites]( [SiteID] [int] IDENTITY(1,1) NOT NULL, [SiteName] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, CONSTRAINT [PK_Sites] PRIMARY KEY CLUSTERED ( [SiteID] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]GOWhat's wrong with that? Why won't godaddy take that?
View 5 Replies
View Related
Dec 29, 2006
I don't know if this is unique to GoDaddy: I found the program for working with a MySQL database very intuitive (to the point of compliments). Today I started work with ASP.NET and SQL, and when I went to work with SQL Server (on GoDaddy), it wasn't intuitive, and there was almost nothing on which to click. By chance might there be a tutorial that would help a person who needs to work with SQL Server on a provider like GoDaddy?
View 4 Replies
View Related
Jul 13, 2007
Hello,First, I tried to find the answet to this question, but no luck - so I decided to post it.
When I was creating my applications in asp.net - for the first time I decided to start using external DataSets. I think they are great and work very nice!But once I pushed the application to GoDaddy, I got an error of this nature:I was trying to access the exterbal dataSet like this:Dim productsAdapter As New NorthwindTableAdapters.ProductsTableAdapter()
Dim products as Northwind.ProductsDataTable...And got an error saying that it couldn't find this type (but it worked fine on my local machine in visual studio).So did I miss something that prevented this application working on GoDaddy? or there are some limitations on GoDaddy? ...or something else.Thank you for looking into this for me.Valera
View 7 Replies
View Related
Jul 22, 2007
Hi all, I'm a newbie of SQL. I built a website using SQL 2005, and the data control was using a dataset and wizard,now when I'm uploading the website + database,I learned godaddy doesn't support database uploading.I have to create an empty SQL database on godaddy's sql server, and reDir the connection in config file. my question is, where should I copy my dataset commands to godaddy's sql server?2nd, how do I invoke them in asp.net? thanks in advanceyonk
View 1 Replies
View Related
Aug 27, 2007
I know this has to be answered somewhere, I just need to know, am I going crazy. I have a shared webhost account at Godaddy. My web config was working at one point, but now I am getting this error:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
Now, If I were to look at this, its telling me that it cannot locate the server I am trying to connect to. Below is my connection string and the address I am getting this from is in my information at godaddy. Tell me, is there something that I am doing wrong here?
<connectionStrings>
<add name="Personal" connectionString="Server=whsql-v20.prod.mesa1.secureserver.net;Database=azwd30;User ID=XXXXX;Password=XXXXX;Trusted_Connection=False" providerName="System.Data.SqlClient" /><remove name="LocalSqlServer"/>
<add name="LocalSqlServer" connectionString="Server=whsql-v20.prod.mesa1.secureserver.net;Database=azwd30;User ID=XXXX;Password=XXXXX;Trusted_Connection=False" providerName="System.Data.SqlClient" />
</connectionStrings>
With the same user name and password, I can access the database through Godaddy's site and see my records, and the address 'whsql-v20.prod.mesa1.secureserver.net' is the same as whats on Godaddy's SQL configuration page, the database name is the same, I have double, triple and spent hours checking all that could affect this... I may just be burnt out using Godaddy. It seems I fix one thing, then there is another thing, I fix that, then there is another. I talk to support, and they say, they dont support anything that they have in their sales page for what they offer. I'd rather get support to say somthing like: "You may want to go to google and press the I'm feeling Lucky button. It would be probably be faster and cheaper than using our department, or you can upgrade to our virtual server and have a little more control, not total control but a little more ... what do you say mr Chris?"
Well, I feel almost a little better venting here. Since I have been with GoDaddy, and I live in the same city where they host their servers, I have been in shambles wondering how they can offer all that they do in their sales pitch and then drop a ball and say, we dont support 90% of what you read in the beginning. It almost wants to make you put a huge NoDaddy.com sticker in your back window and drive around town all day in gas with the same money you give to them. Ok, now I am done venting, any Idea's anyone.???
View 2 Replies
View Related
May 19, 2006
Hi,
I have two sql db's. One for company record info, other for users (aspnetdb) I created the users one using the login control and web administration wizard. This all worked well until I had to publish to godaddy. I had to rebuild my company db, but eventually I was able to pull info and all that is working. The problem is, how the heck do I add users? I noticed that it put users table in my db to start. So I manually went into the db on godaddy's servers, and added an application. Then when I tried to add a user manually, it kept giving me errors. Is there an easier way to add website users through godaddy? Something like the web admin tool that comes with visual studio? Man it is driving me crazy!!!
View 10 Replies
View Related
Nov 7, 2006
I have a vb.net web page which I created (a shopping cart). I created this site using vb.net 1.1 and SQL 2000 on my laptop. On my laptop everything works properly, but when I tried moving it to GoDaddy (http://currahee3-506.org/px/default.aspx). In the code, each query to the SQL server is done using stored procedures.
When you first go the page, a drop down is presented. When you select "Shirt" from the category drop down. A second drop down menu is then displayed and will let you select what type of shirt you are interested in purchasing. Select Polo Shirt Without Pocket. Upon doing so, I receive an error message:
Invalid object name 'tbl_LookupCategory'.Invalid object name 'tbl_pxProducts'.Invalid object name 'tbl_LookupColor'.Invalid object name 'tbl_LookupSizes'.Invalid object name 'tbl_pxQtySold'.Invalid object name 'tbl_pxImages'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'tbl_LookupCategory'.Invalid object name 'tbl_pxProducts'.Invalid object name 'tbl_LookupColor'.Invalid object name 'tbl_LookupSizes'.Invalid object name 'tbl_pxQtySold'.Invalid object name 'tbl_pxImages'.Source Error:
Line 143: sqlCommand.Parameters.AddWithValue("@str_ItemName", Trim(DDL_Items.SelectedItem.Text))
Line 144:
Line 145: Dim dataReader As SqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection)
Line 146:
Line 147: dl_Description.DataSource = dataReader
Source File: d:hostingcurraheepxdefault.aspx Line: 145
Keep in mind the Stored Procedure works in my testing environment, but not on GoDaddy. I moved the selected statement into a view on the GoDaddy SQL server, and the view worked perfectly. The tables do exist on the GoDaddy SQL Server.
This is the Stored Procedure:
CREATE PROCEDURE [dbo].[sp_GetItemInfo]
@str_ItemName VarChar(50)
AS
SELECT TOP 100 PERCENTtbl_pxProducts.int_ItemID,tbl_pxProducts.str_ItemName,tbl_pxProducts.int_RetailPrice,tbl_pxImages.str_SmallImageURL,tbl_pxImages.str_MediumImageURL,tbl_pxImages.str_MediumImageBackURL,tbl_pxImages.str_LargeImageBackURL,tbl_pxImages.str_LargeImageUrl,tbl_pxImages.str_SmallImageBackURL,tbl_pxProducts.int_Quantity - tbl_pxQtySold.int_QtySold AS int_QtyAvailable,tbl_LookupCategory.str_Category,tbl_pxProducts.Int_SizeID,tbl_LookupSizes.str_Size,tbl_pxProducts.Int_ColorID,tbl_LookupColor.str_Color
FROMtbl_LookupCategoryINNER JOINtbl_pxProductsONtbl_LookupCategory.int_CategoryID = tbl_pxProducts.int_CategoryIDINNER JOINtbl_LookupColorONtbl_pxProducts.Int_ColorID = tbl_LookupColor.int_ColorIDINNER JOINtbl_LookupSizesONtbl_pxProducts.Int_SizeID = tbl_LookupSizes.int_SizeIDLEFT OUTER JOINtbl_pxQtySoldONtbl_pxProducts.int_ItemID = tbl_pxQtySold.int_ItemIDLEFT OUTER JOINtbl_pxImagesONtbl_pxProducts.int_ItemID = tbl_pxImages.int_ItemID
WHERE(tbl_pxProducts.int_Quantity - tbl_pxQtySold.int_QtySold > 0)AND(tbl_pxProducts.str_ItemName = @str_ItemName)
ORDER BYtbl_LookupSizes.str_Size
Does anybody have any suggestions as to why this stored procedure is failing?
View 1 Replies
View Related
Apr 13, 2007
Hello all,
I am hosting at Godaddy and using their SQL 2000.
Before, I used to use "Try, Catch and Finally" for my SQL connection. However, I received this error "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached." so often that I changed it to "Using { .... }". Yes, the error doesn't come back as often as before.
Today, I encounter that problem again and my connection to the db is locked, because of that error. Is there away to prevent this from happening? Should I check to see if the connection is still opened before open another one? In case like that, if the connection is still opened, should I close it first before opening a new one OR can I use it too?
Any advice would be appreciated. Cheer ...
View 8 Replies
View Related
Apr 22, 2006
Everything works great on my development box. I am using GoDaddy for production (ASP.Net v2, SQL 2000).
I am not receiving any errors, so I am stumped; no data from the database is displaying on the GoDaddy pages.
I updated the connection string in web.config to this:
< add name="snsb" connectionString="
Server=whsql-vXX.prod.mesaX.secureserver.net;
Database=DB_42706;
User ID=username;
Password=pw;
Trusted_Connection=False" providerName="System.Data.SqlClient" / >
But I am unsure if this is the issue?? Any insights? This is the page I am working on: www.sugarandspicebakery.com/demo/bakery/default.aspx. So, the page displays fine, but it should be showing data from the database. This particular page uses a DataList with ItemTemplate. There is definitely data in the database, and I have even ran the same exact query from the code using the Query Analayzer on GoDaddt and it returned results
I know there isn't much info to go by, but I am hoping someone has some insight since I have been trying to figure this out for days now!
Thank youJennifer
View 2 Replies
View Related
May 5, 2006
Okay... GoDaddy's driving me nuts!!! Anyone... please help.
I am doing an insert into a database with:
INSERT blah blah blah; SELECT @@IDENTITY;
On EVERY OTHER SERVER I have tried this on (local SQLExpress 2005's and my companies various SQL2000 servers, this always returns for me the row number (of autoIndex number of the row just added to the database). My autoIndex was created with the following code:
ALTER TABLE sites ADD autoIndex INT NOT NULL IDENTITY (1,1) PRIMARY KEY
The code I have tried getting the identity with is:
cmd.CommandText = "INSERT blah blah; SELECT @@IDENTITY;";
string autoIndex = Convert.ToString(cmd.ExecuteScalar());
cmd.Dispose();
I also tried:
// bulding of SQL INSERT here
cmd.ExecuteNonQuery();
cmd.Dispose();
cmd = new SqlCommand(SQLDataSource_pics.SelectCommand, conn);
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT @@IDENTITY;";
string autoIndex = Convert.ToString(cmd.ExecuteScalar());
Session.Add("autoIndex", autoIndex);
Most of the time, I get a value of "0" (zero) for autoIndex. Every now an then (5%) of the time I get back a correct value.
I don't know where to go from here.
Thank you.
Hayedid
View 4 Replies
View Related
Jun 29, 2007
I have an application that utilizes aspnet for membership.
The application utilizes the same database (in SQLServer) for storage of other things. I have a view as follows:
USE [DB_Name]
GO
/****** Object: View [dbo].[Authorization] Script Date: 06/29/2007 15:27:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW Authorization
AS
SELECT dbo.Domain.Domain_ID, dbo.Domain.Domain_Name, dbo.Alias.Alias_Name,
dbo.Alias.Alias_Authority, dbo.Domain.Domain_ProcessType, dbo.Domain.Domain_Status,
dbo.aspnet_Users.UserName AS Owner_Name
FROM dbo.Alias INNER JOIN
dbo.Domain ON dbo.Alias.Alias_Parent_ID = dbo.Domain.Domain_ID INNER JOIN
dbo.aspnet_Users ON dbo.Domain.Domain_Owner_ID = dbo.aspnet_Users.UserId
This works fine locally. However, I am hosting this on GoDaddy and though it allows me to create the table, it seems to have a problem (I'm guessing because it is referencing differing schemas?) and I get an error of: Incorrect syntax near the keyword 'Authorization'.through the SQLServer Web interface whenever I attempt to view the data from this view.
I don't doubt that there is some kind of difference between the manner that SQLServer has set up the schemas and how they are set up on my local version.
Any help would be greatly appreciated. I am at a loss of how to get this info and truly want to utilize the view as it would improve performance over a query - at least I believe so. My thinking is that although there may be a performance drop when the item is being added, I gain on the back-end where I really need the performance over utilizing a query.
Thanks,
Steven Henley
View 3 Replies
View Related
Mar 4, 2007
Hello,
I'm hosting with GoDaddy and I receive this error:
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
For sure the problem is "MAX POOL SIZE WAS REACHED". How do I solve this problem? Or how do I prevent this from happening in the future?
Now, I cannot open any of my pages on GoDaddy.
Any advice would be appreciated. Thank you.
View 1 Replies
View Related
Oct 12, 2007
Hello,I just got a personal site with godaddy that I would like to be able to connect to using SQL Server management studioI try to connection using Server Name: sdhfuh928323984ujf.phx3.secureserver.netauthentication: sql serverUsername: my usernamepassword: my passwordThen it tries to connect and gives me an error that I can't and remote connections may not be allowed. I followed the directions it gave and turned on remote connections, restarted the server, but I still get the error.any ideas?
View 3 Replies
View Related
May 1, 2008
Hello, we have the hosting account with GoDaddy Server. We have using Visualstudio.net and MSSSQL Server 2005.
For hosting the files for the remote server ( Godaddy ) we are using FTP Cute client to upload the files. For Uploading a database they given a seperate login and we are hosted my data as a Transact SQL File. Up to this its going fine.
After that I want to retrieve my data from GoDaddy Database server. They are not allow the remote connections directly. But they told to use the File DSN to retrieve the data from thier server ( GoDaddy Server ). I don't Know how to use thier File DSN to connect My ASP.Net 2.0 Application to thier GoDaddy Database Server.
If I asked to GoDaddy Support team they are sending this file.
Connecting to a Microsoft SQL Server Database Using ASP/ADO
View 10 Replies
View Related
Apr 5, 2007
I have a simple Website and Database created in VWD exp.
I've just added a windows account to GoDaddy.com.
Unfortunately there is no way to port your database on to the GoDaddy server directly, but it says you must instead, recreate the database by copying and running SQL statements from a file called "personal-add.sql". (their "help" page: http://help.godaddy.com/article.php?article_id=695)
I can't find any such file. Can someone please let me know what I'm missing here?
Thanks.
View 4 Replies
View Related
Jun 6, 2007
Dear Advance,I used one stored procedure to retrive 3 different result set. and in the codebehind i seperate it. means from the dataset i seperate three different datatable and then show my data as my need.but the main problem is ... after retriving the datafrom the database i have to user foreach loop to bind the coulmns data to my different custom class.example: foreach (DataRow oDrow in MyDataTable.Rows) {oClass=new Class();oClass.Name1=oDrow["Name1"] .toString();oClass.Name2=oDrow["Name2"] .toString();.... } 1. so my first question is there any optimization possible ?2. my result set is too loong ... so should keep just one hit to database or hit more than one time Currently i am optimizing my web application. in the previous version 1 have to hit the database 3/4 times for different purposes. but now it hits only one time... but it takes time in the codebehind to perform different operation.Any Suggestion
View 1 Replies
View Related
Dec 11, 2001
I have a SP that calls about 10 stored procedures sequentially. The 10 SP's are basically complex update statements, each one individual. Is there any way to optimize this?
I know putting the 10 into 1 SP would make it compile faster but thats about it. Are there any execution tricks of Stored Procedures firing off sequentially?..or anything I should know?
-thanks
Bartman
View 2 Replies
View Related
Mar 2, 2001
Hello All,
What is the best way to optimize this code or rewrite it using ISNULL ?
CREATE PROCEDURE get_employees (@dept char(8), @class char(5))
AS
IF (@dept IS NULL AND @class IS NOT NULL)
SELECT * FROM employee WHERE employee.dept IS NULL
AND employee.class=@class
ELSE IF (@dept IS NULL AND @class IS NULL)
SELECT * FROM employee WHERE employee.dept IS NULL
AND employee.class IS NULL
ELSE IF (@dept IS NOT NULL AND @class IS NULL)
SELECT * FROM employee WHERE employee.dept=@dept
AND employee.class IS NULL
ELSE
SELECT * FROM employee WHERE employee.dept=@dept
AND employee.class=@class
Thanks in advance.
-Praveena
View 1 Replies
View Related
Jul 6, 2001
I am wondering if the size of the data file makes a difference in running Insert's and/or doing Fetch's. Our DB was 11GB in size, I ran a dbcc shrinkdatabase and it shrank it to 5.5 GB in size, now that it is smaller will it run a select query faster as opposed to when we run large inserts and it has to automatically grow to accommodate the insert. I am trying to figure out if I should leave my .mdf file large or keep it small or does it even make a difference. I am only doing large inserts while loading data to get ready for production after that the inserts will be hourly but much smaller, however our queries to the DB after it is in production will be much more intensive.
Hope this makes sense. Appreciate your help.
View 2 Replies
View Related
Jan 9, 2006
Hi All,
I am writing a script that runs the optimization process. I am not sure how to test it? Any help is greatly appreciated.
View 9 Replies
View Related
Dec 20, 2006
Hello,
Is there a tool to optimize SQL procedures code?
Thanks,
Miguel
View 4 Replies
View Related
Jul 20, 2005
We're building a company wide network monitoring systemin Java, and need some advice on the database design andtuning.The application will need to concurrently INSERT,DELETE, and SELECT from our EVENT table as efficiently aspossible. We plan to implement an INSERT thread, a DELETEthread, and a SELECT thread within our Java program.The EVENT table will have several hundred million recordsin it at any given time. We will prune, using DELETE, aboutevery five seconds to keep the active record set down toa user controlled size. And one of the three queries willbe executed about every twenty seconds. Finally, we'llINSERT as fast as we can in the INSERT thread.Being new to MSSQL, we need advice on1) Server Tuning - Memory allocations, etc.2) Table Tuning - Field types3) Index Tuning - Are the indexes right4) Query Tuning - Hints, etc.5) Process Tuning - Better ways to INSERT and DELETE, etc.Thanks, in advance, for any suggestions you can make :-)The table is// CREATE TABLE EVENT (// ID INT PRIMARY KEY NOT NULL,// IPSOURCE INT NOT NULL,// IPDEST INT NOT NULL,// UNIXTIME BIGINT NOT NULL,// TYPE TINYINT NOT NULL,// DEVICEID SMALLINT NOT NULL,// PROTOCOL TINYINT NOT NULL// )//// CREATE INDEX INDEX_SRC_DEST_TYPE// ON EVENT (// IPSOURCE,IPDEST,TYPE// )The SELECTS areprivate static String QueryString1 ="SELECT ID,IPSOURCE,IPDEST,TYPE "+"FROM EVENT "+"WHERE ID >= ? "+" AND ID <= ?";private static String QueryString2 ="SELECT COUNT(*),IPSOURCE "+"FROM EVENT "+"GROUP BY IPSOURCE "+"ORDER BY 1 DESC";private static String QueryString3 ="SELECT COUNT(*),IPDEST "+"FROM EVENT "+"WHERE IPSOURCE = ? "+" AND TYPE = ? "+"GROUP BY IPDEST "+"ORDER BY 1 DESC";The DELETE isprivate static String DeleteIDString ="DELETE FROM EVENT "+"WHERE ID < ?";
View 2 Replies
View Related
Jul 17, 2007
There are two main tables in my app,in order to optimize search via scope condition, I set many indexs for these two tables
however,at the same time the two tables are also used for my etl app,everyday there are more than thousands of data need to be updated or inserted, but index is not suitable for huge modification,any idea about how to handle this?
thanks in advance
View 4 Replies
View Related
Oct 15, 2007
Hello,
what is the meaning about <MissingIndexGroup Impact="99.9521"> in the Queryplan? Should I create a Grouped Index? An what is the meaning about Impact="99.9521"?
If the Impact =100 you get a 100% better performance, and if the impact =20 ypu get a 20% better performance, is this the meaning?
finchen
View 4 Replies
View Related
Aug 15, 2007
Hi,
Can anyone help me optimize the SELECT statement in the 3rd step? I am actually writing a monthly report. So for each employee (500 employees) in a row, his attendance totals for all days in a month are displayed. The problem is that in the 3rd step, there are actually 31 SELECT statements which are assigned to 31 variables. After I assign these variable, I insert them in a Table (4th step) and display it. The troublesome part is the 3rd step. As there are 500 employees, then 500x31 times the variables are assigned and inserted in the table. This is taking more than 4 minutes which I know is not required :). Can anyone help me optimize the SELECT statements I have in the 3rd step or give a better suggestion.
DECLARE @EmpID, @DateFrom, @Total1 .... // Declaring different variables
SELECT @DateFrom = // Set to start of any month e.g. 2007-06-01 ...... 1st
Loop (condition -- Get all employees, working fine)
BEGIN
SELECT @EmpID = // Get EmployeeID ...... 2nd
SELECT @Total1 = SUM (Abences) ...... 3rd
FROM Attendance
WHERE employee_id_fk = @EmpID (from 2nd step)
AND Date_Absent = DATEADD ("day", 0, Convert (varchar, @DateFrom)) (from 1st step)
SELECT @Total2 ........................... same as above
SELECT @Total3 ........................... same as above
INSERT IN @TABLE (@EmpID, @Total1, ...... @Total31) ...... 4th
Iterate (condition) to next employee ...... 5th
END
It's only the loop which consumes the 4 minutes. If I can somehow optimize this part, I will be most satisfied. Thanks for anyone helping me....
View 11 Replies
View Related
Jun 23, 2004
Hi guys,
Could any one tell me what is the best way to declare a connection from ASP .net to a SQL database so the sql could support the maximum users, because it seems that the way i'm using is not correct cuz when i make some transactions from my website to the database, the database send an error message saying that there are no more free connections.
Thnx
View 1 Replies
View Related
Apr 28, 2000
This may sound a little silly, but does anyone have any words of wisdom on how to optimize a server/database for minimim rollback? We have some multimillion row tables we were trying to do updates against, and after several days they increased the size of the transaction log to the point they filled up the drive the database files/logs were on. We've now been running a rollback for about five days. I'd like to make sure this doesn't happen again.
View 1 Replies
View Related
Apr 19, 2000
I am using the Database maintenance on a database that is about 4gb. The database optiiztion is running about an hour. Does this job only do an update stats? If I run the stored procedure sp_updatestats on the database it only takes a couple of minutes. Are thes two processes doin the same thing? Do I need them if the create, update statistics are turned on?
Thanks,
Glen
View 4 Replies
View Related
Feb 19, 2001
Trying to optimize a query, and having problems interpreting the data. We have a query that queries 5 tables with 4 INNER JOINS. When I use INNER HASH JOIN, this is the result:
(Using SQL Programmer)
SQL Server Execution Times:
CPU time = 40 ms, elapsed time = 80 ms.
Table 'Table1'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.
Table 'Table2'. Scan count 1, logical reads 40, physical reads 0, read-ahead reads 0.
Table 'Table3Category'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'Table4'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.
Table 'Table5'. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0.
When I use INNER JOIN, this is the result:
SQL Server Execution Times:
CPU time = 10 ms, elapsed time = 34 ms.
Table 'Table1'. Scan count 4, logical reads 10, physical reads 0, read-ahead reads 0.
Table 'Table2'. Scan count 311, logical reads 670, physical reads 0, read-ahead reads 0.
Table 'Table3'. Scan count 69, logical reads 102, physical reads 0, read-ahead reads 0.
Table 'Table4'. Scan count 69, logical reads 98, physical reads 0, read-ahead reads 0.
Table 'Table5'. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0.
Now, when timing the code execution on my ASP page, it's "faster" not using the HASH. Using HASH, there are a few Hash Match/Inner Joins reported in the Execution Plan. Not using HASH, there are Bookmark Lookups/Nested Loops.
My question is which is better to "see": Boomark Lookups/Nested Loops or Hash Match/Inner Joins for the CPU/Server?
Thanks.
View 1 Replies
View Related
