Query Performance Issue - Need Direction
Nov 2, 2007
My company has developed a VB.NET 2003 application which interfaces with the SQL Server database of another software package. We are experiencing serious performance differences at one of our client sites on a particular query that is one of the frequent queries that runs in our software which I will try to outline below.
Our database and the accounting system database are in separate instances on the same SQL Server 2005 box. Our query is a simple SELECT statement with filtering which properly utilizes the built-in indexes for that table. In their case it returns roughly 13000 records (vendor listing). There is no report of slowness regarding other queries as none of them return the volume that this particular query does.
On one offending workstation: if we run this query, it takes less than a second to get results back in SQL Management Studio's query window. This is consistent with the accounting software which takes about 2 seconds to run the query and display the results in a grid (the accounting system is Viewpoint, a leading construction accounting system written in VB6). In our system, attempting to do the same thing as the accounting system, it is taking nearly 16 seconds to retrieve and display the results. This workstation is running on WIndows XP SP2. They are experiencing this same behavior on several workstations.
At the same client site, they have identified two Windows 2000 workstations which can run the query and display the results from our system in approx. 2 seconds - which is what we would expect and is also what we experience internally on our development platform, and 12 other clients who run our software and interface with the same accounting system - regardless of OS on the workstation.
Based on the results in the query window and the results on the 2000 workstations and the success of other clients who have as many records if not more than this particular client - i don't think we have an issue with the query or the code. The results are very consistent on each workstation as well so I don't think it's a network traffic or activity issue. My gut points me at .NET Framework version differences - since the things that would affect this process are framework items - SQL Server OLEDB drivers and objects, DataGrids, Datatables and DataViews,etc.
On one of the XP workstations I had them remove .NET framework 3.0 which had no effect on the problem, but they also have v. 1.0.3705, 1.0 Hotfix (KBB886906), Framework 1.1, 1.1. Hotfix (KBB886903), Framework 2.0 with 2 security updates (KB917823 and BK922770).
Can anyone point me in a direction on things that I can check that might be different between their XP and 2000 workstations which might affect this process? I am working on some caching logic which will reduce the number of times this query is run, but I would like to understand why there is a behavioral difference.
Any help is appreciated. Please let me know if there is any other information I can provide.
Thanks.
Paul
View 2 Replies
ADVERTISEMENT
Feb 21, 2007
We recently implemented merge replication.We were expereincing. The replication is between 2 SQL Servers (2005) over same network box, and since we have introduced the replication, the performance has degraded considerably on subscriber end.
1) One thing that should be mention is that its a "unidirectional Direction" flow of changes is from publisher towards subscriber (only one publisher and distributor as well and one subscriber ).
2) Updates are high than inserts and only one article let say "Article1" ave update up to 2000 per day and i am experiecing that dbo.MSmerge_upd_sp_Article1_GUID taking more cpu time.what should be do..
on subscriber database response time is going to slow and i am experiencing a lot of number of LOCK time outs on application end.
can any one can also suggest me server level settings for aviding locking time out.
looking for any experieced solution/suggestion.
Thanks in advance.
View 3 Replies
View Related
Jun 23, 2006
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies
View Related
Jun 1, 2008
I am the owner of a small business and run sports leagues on my website. I have been using a csv file to gather registrations from my webform and then manually importing them to Access. I also use the same method for collecting their results each week. Can I use SQL to automate my process? If so, where to I get the training I need to set that up? I'd like my customers to be able to log in to their account on the website and post their results (which attach to their name/profile). Then I'd like to extract that information in a report that I can post to the website. Or maybe have the customer sort the results by different criteria types.
Can I do that with SQL? More importantly, am I even in the right ballpark for getting this done?
Thanks to anyone who can give me a nudge in the right direction.
View 2 Replies
View Related
Apr 9, 2008
My table structure:
Aircraft_Id(PK, nchar(25),not null)
Service_Ceiling_ft (numeric(18,0), not null)
My Data:
Aircraft_Id Service_CeilingP_ft
F-14 Tomcat 53000
My Query:
SELECT 'Aircraft_Id, 'Service_Ceiling_ft'
FROM Combat
WHERE 'Service_Ceiling_ft' > '51000'
My result:
(No column name) (No column name)
Aircraft_Id Service_Ceiling_ft
I'm not getting any error messages or data. Any advice would be a great help.
thanks
US Navy - We are fueled, armed, and go for launch.
View 3 Replies
View Related
Apr 15, 2008
this is a seemingly simple query, but i've been googling around for a while and haven't been able to come up anything, probably because i simply can't explain what I'm trying to do
can someone please point me in the right direction? using sql server 2005.
tbl_vendorprice looks like:
id vend price
1 A 2.00
1 B 3.00
2 A 4.00
2 B 3.50
3 A 8.00
3 B 8.50
and this is what i want as a result
id vendA VendB
1 2.00 3.00
2 4.00 3.50
3 8.00 8.50
thanks
David
View 2 Replies
View Related
Apr 23, 2007
Charter Cable does not provide server technology to residential customers.
I know nothing about SQL. I believe that I read that it can be setup on my computer for testing web pages.
I may not even be in the right forum.
Would someone please direct me where to start?
Thanks
All help is appreciated.
Thanks
View 3 Replies
View Related
Aug 5, 2004
Is it possible to have a SqlParameter setup as an Output, and get it to return @@Identity without using a stored procedure?
I have an INSERT statement, written as TEXT in my code (at the time being, I cannot create stored procedures). I'm trying to find out how to return the created IDENTITY that was generated.
Can someone please explain to me how this works?
Thanks.
View 1 Replies
View Related
Feb 11, 2007
Hello, thanks for reading this.
I am trying to learn a bit about debveloping my own website, and I am in need of a basic direction on how to acomplish this task:
I have an local program running on my PC that creates an MS Access database export of my inventory. This export can be directly uploaded to my web server in a zip format.
My web site is developed in Coldfusion MX7, and uses a MS SQL 2005 database. What I would like to do is create somewhat of an "Auto update" for my website, exporting my access database and updating the MS SQL database automatically.
I really can use some good direction on this problem.
Thank you for any help in advance.
View 4 Replies
View Related
Nov 24, 2007
hi.
i am a c++ developer who requires to learn sql.
i have no other programs than the sql server that comes with visual studio pro 2005.
what
do i need to get/do to create a sql databse and to manipulate it in c++.
thank you.
View 5 Replies
View Related
Jan 23, 2007
Hi all. The company I work for is looking for a new SQL server. Where can I find information and or a tool for sizing information? By sizing information I mean how big a pile of hardware am I going to need to run MS SQL for x number of connected users with x size database, etc. I've been tooling around the internet and MS' site but can't find any info on this.
Can anyone shed some light for me?
View 4 Replies
View Related
Feb 2, 2007
Happy Friday afternoon, all,
My task is seemingly simple. I have data on the server in MS Excel Files. I need to get the data into multiple tables in a SQL Server db on the same server.
I have been only working with SSIS for a bit, so please bear with me.
I can load the data directly from the Excel worksheet to one table, but I need to run an already defined stored procedure on the data from Excel before putting it into tables. I need to loop over all the rows and run the data from each row through the stored procedure.
So, I think I need an Execute SQL Task withing a For Each Loop, but neither is available on the Data Flow page, and I don't see how to use them in the control flow page. I don't see that any of the Data Flow transformations which are available on the dataflow page will do what I need.
I can have created the data flow Source-Query and the Destination-Query; it's the bit in between that has me hung up.
Can anyone please give me a high level overview of what I need to do, or point me to an example of something similar to what I am trying to do?
Thanks and have a great weekend,
Kathryn
View 11 Replies
View Related
May 4, 2007
Hi,
In the SqlDataSource control if I go to the SelectQuery property and I set one parameter with the “direction� property to “Output� the result doesn’t display in the control, why?
Points:
The procedure witch is in the selectquery property the parameter in it is set to output two.
View 3 Replies
View Related
Sep 14, 2000
Has anyone created a stored procedure that notifies a customer that their order_status has been changed from 'submitted' to 'backordered' or from 'backordered' to 'shipped'? Once written, how does the sp get automatically executed? Does anyone have an example or a book/site with an example?
--Thanks in advance.
View 1 Replies
View Related
Nov 9, 2007
We lost our Datawarehouse developer that set up this application for us, so I am now in charge... ick, and don't know what I'm doing really.
Lately no messages leaving sys.transmission_queue... all have transmission_status of: "One or more messages could not be delivered to the local service targeted by this dialog."
Setup looks like this:
CREATE MESSAGE TYPE XmlSubmission VALIDATION = WELL_FORMED_XML;
CREATE CONTRACT XmlContract (XmlSubmission SENT BY INITIATOR);
CREATE QUEUE ReceiverXmlQueue WITH STATUS = ON, ACTIVATION (PROCEDURE_NAME = MMRS.MMRS.WrapXmlProcessor, MAX_QUEUE_READERS = 1, EXECUTE AS SELF );
CREATE QUEUE SenderXmlQueue;
CREATE SERVICE Sender ON QUEUE SenderXmlQueue;
CREATE SERVICE Receiver ON QUEUE ReceiverXmlQueue(XmlContract);
ALTER QUEUE ReceiverXmlQueue WITH STATUS = on
Service Broker is enabled on database, and I've tried dropping and recreating these objects to no avail.
When functioning, these messages get recieved and procecessed into a staging SQL table, storing the XML to be loaded up into the relational tables.
Any ideas or clues for troubleshooting would be greatly appreciated!
View 1 Replies
View Related
Jun 22, 2006
Hi all,
I have problem to change direction of the textbox, i need to change the driction as bottom to top.
But in report has provide the tb-rl and tb-lr. these are changed the direction as top to bottom.
anybody worked on it , please help.
Thanks
Murugan A
View 12 Replies
View Related
Apr 11, 2007
I have some reports on a report server that needs to be reached in two ways.
1 - From a windows client application (over internet)
2 - From a web browser (over internet)
Windows authentication could not be used here, since the users are not part of any domain etc, so I have implemented forms authentication for Reporting Services as described here:
http://msdn2.microsoft.com/en-us/library/ms160724.aspx
My first task was to access the reports from the windows forms application, and I managed to do that without too much trouble using a ReportViewer and setting the custom credentials in this way:
myReportViewer.ServerReport.ReportServerCredentials.SetFormsCredentials(null, "loginname", "password", "");
Works great!
However, now I'm dealing with the second problem... accessing the reports from the web. Sure, this works fine if i just type http://myserver/reports/. This brings up the custom loginpage and if I enter correct login information I get access to the reports... The problem is that I don't want to show everything 'around' (on top of) the report, I just want to show a report. Lets say I have a webpage with two frames. One on the left with a couple of links each representing a singel report. When clicking the link the report should be rendered in the right frame.
What approach should I use here? When and where should the user authentication take place?
I have done some testing with an ASP.NET application using a report viewer, but I seem to get the error:
Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'
I guess this is because Reporting services wants to bring up the login page? I can't find any way of setting the credentials for the reportViewer in my ASP.NET app, like I did in the WinForms app. I'm really not a web-developer so I might be missing some obvious points here, if I do, please point me in the right direction...
I have also read about using the Report server web service to render the html-page without using the reportviewer, but when I try to make a call to a service like this:
ReportingService _rs = new ReportingService();
_rs.Url = "http://myServer/ReportServer/ReportService.asmx";
CatalogItem[] items = _rs.ListChildren("/", true);
I get an exception like the one above, telling:
Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'
Where should I go from here... any help would be much appriciated!
Regards Andreas
View 2 Replies
View Related
Jan 30, 2008
How can you set the text direction to BT-RL 'bottom to top' in SRS? There is control for TB-RL but not BT-RL. Need this for SRS label printing application.
View 1 Replies
View Related
Sep 7, 2006
Hi.
I am wondering if somebody out there wouldnt mind
looking over my stored procedure?
The idea behind this procedure is (was) that,
(they are numbered...)
1) the procedure checks for the existence of data in the database and
2) if no data exists (if the previous statement throws an error), then execute the insert statement
3) otherwise (data exists), execute the update
I also have "sub" error handling for each statement so, if it is executing the insert, return 99 for fail, 0 for success. the same for update.
problem is that its returning the values for the first select statement and i have no idea what is going on fir the Insert or Update statements as i cant set break points in a stored procedure.
thanks for any feedback.
Code:
ALTER PROCEDURE dbo.sp_UpdateEditProfile
(
@headline VARCHAR(50) = NULL,
@about_me TEXT = NULL,
@email_address VARCHAR(50) = NULL,
@edit_date VARCHAR(50) = NULL
)
AS
BEGIN
SELECT @edit_date = CONVERT(DATETIME,convert(char(26), getdate(), 109))
END
BEGIN
--check to see if user has data there already
1)SELECT headline, about_me FROM EditProfile WHERE email_address=@email_address
--if he doesn't,
IF @@ERROR <> 0
--this is an insert statement
BEGIN
SET ANSI_WARNINGS OFF --ansi errors on text insert
2)INSERT INTO EditProfile
(headline, about_me, email_address, edit_date)
VALUES (@headline, @about_me, @email_address, @edit_date)
SET ANSI_WARNINGS ON
IF @@ERROR <> 0
BEGIN
RETURN(99)
END
ELSE
BEGIN
RETURN(0)
END
END
--if he does
ELSE
--update the data
BEGIN
SET ANSI_WARNINGS OFF --ansi errors
3) UPDATE EditProfile
SET headline=@headline, about_me=@about_me, edit_date=@edit_date WHERE email_address=@email_address
SET ANSI_WARNINGS ON
IF @@ERROR <> 0
BEGIN
RETURN(99)
END
ELSE
BEGIN
RETURN(0)
END
END
END
View 1 Replies
View Related
Mar 1, 2007
I am using the MS SQL Server Management Studio Express to create a stored procedure in one of my databases. I specify one of the parameters as OUTPUT as follows:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ProcRetDbl]
@Threshold real, @Result real OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT @Result = Channel1 FROM DataTable WHERE Channel2 < @Threshold
END
But then when I look at the properties of the @Result parameter in the Object Explorer's tree, it is shown as "Input/Output". Now, this seems like no problem at all since it will work fine as output, even though I don't need it to be able to do input as well, but I'm wondering why that is happening.
I am using ADO.Net on the other end to execute the procedure and I need to decide what parameter type to set to the SqlParameter object: "Output" or "InputOutput". I'm sure I can sort this out but I usually like to know what I'm doing. Thanks for the help.
Kamen
View 2 Replies
View Related
Oct 24, 2006
Hello, I have an existing suite of applications in my company where remote laptop users enter data into a local database instance. There are several different applications with several different databases (some users run application A and C, others run A and B and D, etc). All of this data needs to make its way into both their local databases, and to the front office database. In addition, some of the data goes out of the front office to other end users (supervisors get data from people under them).
Currently, we get the data back and forth by creating text files full of SQL statements that are run on either the end user servers or the main server, and we zip and ftp these SQL Script files back and forth.
I am looking into the benefits of replacing this process with a Service Broker application, but I'm still too much of a newbie to understand how this might work. What should my messages contain? Should they contain the same SQL statements that are currently in the script files, and then a SSB process would grab the SQL and run it? Or should the message types be more specific to each application?
any pointers to articles that go beyond "here's what SSB is" would be appreciated.
matt tagliaferri
View 3 Replies
View Related
May 30, 2007
Hello and thanks for reading,
I would like to know if it's possible to add around 4000 users into the ASPNETDB without adding them one at a time. I have to avoid using a "create user wizard" because this login is for a private online election only. The list of users is in an access database and looks like this:
Username Password
DHunziker Hu8645
SEnderton En0854
BAckler Ac9576
....
Unfortunately, I don't have valid emails for all 4000 people, nor do I have the time and resources to collect security questions and answers so I don't know if this is even possible. Can you help me?
Thanks!
View 6 Replies
View Related
Apr 20, 2007
I have been programming access databases for 8 years.Well today I find out that I need to know SQL.HUH LOLwell I don't have a clue as to how people even enter data intoa sql database or how to manipulate or retrieve data.Can someone please recommend a book to get me started?thank you very much for any and all help with this.
View 2 Replies
View Related
May 17, 2007
When running a business logic handler at the subscriber, you can override SubscriberInserts, SubscriberUpdates and SubscriberDeletes. Are these methods called when data is changed coming from the server to client, from the client to the server, or in both directions?
Thanks,
Bryan
View 1 Replies
View Related
Jul 26, 2006
Hello All,
I just started a new job and I'm totally new to our SQL 2000 server (v. 8.00.760). My boss asked me to look into 'cleaning up the database' and has also mentioned reindexing as well. He seemed to suggest that this was a pretty minor thing to actually do but I'm absolutely clueless on where to begin.
At this point, with having no experience, I'm very hesitant to do anything that may negatively impact our SQL database. I would appreciate any and all advice from more experienced people to help get me pointed in the right direction.
Thanks in advance.
View 1 Replies
View Related
Apr 16, 2008
Hi,
I am developing a report that should be localized dynamically. The report will have both arabic and western users. Therefore I need to use the direction property of some single text boxes, which works fine. But when setting the direction property to RTL for a table it has no affect!
You would assume that the whole tables content should be "reversed" and also the text in the table went from right to left. But no.
When viewing the report in IE and right clicked and click "View Source" you can see that the table do not have a direction style set, even though I set the property on the table in design mode.
I am going crazy about this issue! Could this be a MS bug?
I really need some help here...
PS. I have tried numerous of combinations of the different International properties to get the direction property to work - with no luck.
View 1 Replies
View Related
May 21, 2002
I am somewhat new to DTS packages - please bear with me...
:: Overview:
From a web page, I need to create a text file on a User's mapped drive drive (note: the mapped drive is always the same and it is a drive that the IIS cannot see, and FTP is not an option here). One of the parameters I need to pass from the web page (to the DTS) is the text file name.
Currently, I have a stored procedure that creates my dataset for my text file, and I can create the text file from the web page onto the IIS server, but this does not solve my problem.
My dilemma is how do I create the dynamic text onto the mapped drive? The SQL server CAN see the mapped drive.
Can I bring together what the Stored Procedure creates AND pass thru the dataset and the dynamic name to a DTS so it can create and save the file?
Can the Stored Procedure be eliminated and it all be done via DTS?
What about passing in parameters directly to a DTS from the web page? I'm not finding much help on this portion
Any help and/or direction would be appreciated as I am on a tight deadline!
View 1 Replies
View Related
Aug 23, 2015
I use reporting services in designing my reports. now, I need to change the report layout  dynamically according to a parameter. how to do this.
View 4 Replies
View Related
May 28, 2008
Hi,
We have an existing merged replication schema that works well. One of the
tables is named audit. Currently this is a bidirectional transfer. However,
we want a new audit table that will transfer data from the subscriber to the
publisher and not the other way round. I have a script that was generated as a backup script when replication was first created. I have no idea which parameter to change in order to have the merge replication going from subscriber to publisher. Any advise is helpful. Thanks.
View 1 Replies
View Related
Aug 21, 2007
I am trying to order by the field and direction as provided by input parameters @COLTOSORTBY and @DIR while using a CTE and assigning Row_Number, but am running into syntax errors.
Say I have a table called myTable with columns col1,col2,col3,
Here's what I'm trying to do
with myCTE AS
(
Select
col1
,col2
,col3
,row_number() over (order by
case when(@DIR = 'ASC') then
case when @COLTOSORTBY='col1' then col1 asc
when @COLTOSORTBY='col2' then col2 asc
else col3 asc
end
else
case when @COLTOSORTBY='col1' then col1 desc
when @COLTOSORTBY='col2' then col2 desc
else col3 desc
end
end
from myTable
)
Please let me know what i can do with minimal code repetition and achive my goal of dynamically sorting column and direction. I do not want to use dynamic SQL under any circumstance.
Thanks.
View 7 Replies
View Related
May 19, 2004
Hi..
Is there any specific place where I can find which SQL Query is more efficient?.
Is INNER JOIN is faster or Select ... Where ID in (SELECT ...) is faster?
I have two tables:
1.FLEET (The number of rows is not so much)
Attributes :
Company_Id (PK)
Fleet_Id (PK)
Fleet_Name
Fleet_Description
2.USER_PRIVILEGE (The number of rows can reach up to 3 times the number of row in fleet table)
Attributes :
Company_Id (PK)
Fleet_Id (PK)
User_Id (PK)
Privilege_Id(PK)
Comment
Category
I want to select Fleet_Id and Fleet_Name from fleet table
Where the current user has privilege_id=1
I have two possible select statement :
1.Option 1
SELECT Fleet_Name, Fleet_Id
FROM FLEET
WHERE (Company_Id = 2) AND (Fleet_Id IN
(SELECT fleet_id
FROM user_privilege
WHERE user_id = 11 AND company_id = 2 AND privilege_id = 1))
ORDER BY Fleet_Name
2.Option 2
SELECT F.Fleet_Name, F.Fleet_Id
FROM USER_PRIVILEGE U INNER JOIN
FLEET F ON U.Fleet_Id = F.Fleet_Id
WHERE (F.Company_Id = 2) AND (U.Privilege_Id = 1) AND (U.User_Id = 11)
ORDER BY F.Fleet_Name
Actually which one is faster. Is SQL Statement with INNER JOIN (Option 2) can be executed faster than the one with double Select Statement(Option 1)?
Any suggestion is welcomed.
Thank you very much.
View 1 Replies
View Related
Dec 12, 2005
Hi:
I have the following query, can somebody help me?
SELECT s.Id, s.NameFROM Switch s INNER JOIN SwitchTelephoneRange r ON s.Id = r.IdWHERE '1526858' BETWEEN FromTelephone AND ToTelephone
Where the '1526858' is a phone number.
My problem is, I want to run the above query for each record in :
select Telephone from PhoneDirectory
So, each telephone number in the second phone, would macth the ' ' in the first query.
How can I do so? Do I need a loop? a cursor? Can you help please?
Thanks
View 5 Replies
View Related
Nov 14, 2003
Hi,
I want to know query execution time as output. I want execution time only. this is for tuning purpose... Time displayed in the status bar is not helpful for me.
thanks.
View 1 Replies
View Related
