Peculiar Performance Issue.
Dec 1, 2007
Hi all,
We have a issue with the performance in SQL server database.
Scenario & Issue:
We have delivered a .net application to our client. This application is installed in newly built windows 2003 server.
The client is facing performance issues with the application. When compared with the performance in the development server , the performance of the production server is very poor.
Even when we execute the stored procedures in the backend, the performance is poor in the production server.
Example: A stored procedure that takes 16 seconds in the development server takes 17 minutes for the same parameters. The time remains the same even for HOT execution.
System Info:
Database Version - SQL Server 2005
Database Size - 120 plus GB
OS Platform - Windows 2003
Database Load - 50 users
CPUs - 4
RAM - 8 GB
The OS is Clustered ( failover clustering ).
Points to Note:
1.There is a huge table with 250 million rows ( this table itself takes upto 60 GB )
2.The huge table is partitioned ( SQL server 2005 table partitioning ) and placed in 20 different filegroups (.mdfs).
3.The .mdf's are placed in a SAN and .ldf is in local HD
4.Dynamic queries are used at few instances for performance benefits.
Questions:
1. Any thoughts on why this kind of performance issue arises ?
2. The client DBA wants us to clear the data and stored procedure cache before executing the stored procedure and test the performance.
Will this be would be the case in production scenario ?
3. Will the performance change based on the input parameters ?
4. The client DBA also have stated that a report server that pings the production database server is the cause for frequent clearing of the SQL Server cache.
When does the SQL Server database actually clears the cache memory? Is there any way to control it?
Any help would be highly appreciated.
Thanks & Regards,
Hariarul
View 12 Replies
ADVERTISEMENT
Dec 1, 2007
Hi all,
We have a issue with the performance in SQL server database.
Scenario & Issue:
We have delivered a .net application to our client. This application is installed in newly built windows 2003 server.
The client is facing performance issues with the application. When compared with the performance in the development server , the performance of the production server is very poor.
Even when we execute the stored procedures in the backend, the performance is poor in the production server.
Example: A stored procedure that takes 16 seconds in the development server takes 17 minutes for the same parameters. The time remains the same even for HOT execution.
System Info:
Database Version - SQL Server 2005
Database Size - 120 plus GB
OS Platform - Windows 2003
Database Load - 50 users
CPUs - 4
RAM - 8 GB
The OS is Clustered ( failover clustering ).
Points to Note:
1.There is a huge table with 250 million rows ( this table itself takes upto 60 GB )
2.The huge table is partitioned ( SQL server 2005 table partitioning ) and placed in 20 different filegroups (.mdfs).
3.The .mdf's are placed in a SAN and .ldf is in local HD
4.Dynamic queries are used at few instances for performance benefits.
Questions:
1. Any thoughts on why this kind of performance issue arises ?
2. The client DBA wants us to clear the data and stored procedure cache before executing the stored procedure and test the performance.
Will this be would be the case in production scenario ?
3. Will the performance change based on the input parameters ?
4. The client DBA also have stated that a report server that pings the production database server is the cause for frequent clearing of the SQL Server cache.
When does the SQL Server database actually clears the cache memory? Is there any way to control it?
Any help would be highly appreciated.
Please let me know if you have any questions.
Thanks & Regards,
DBLearner
View 2 Replies
View Related
Apr 4, 2007
Hi all,
When I execute a SELECT query on a table with around 25 million records, I get
performance difference based on the passed parameter value .
The below queries returns the output in 1 second.
SELECT TOP 10000 * FROM TestTable WHERE Column = 1
SELECT TOP 10000 * FROM TestTable WHERE Column = 2
SELECT TOP 10000 * FROM TestTable WHERE Column = 3
The below query alone takes 18 seconds to return the output.
SELECT TOP 10000 * FROM TestTable WHERE Column = 4
(FYI: The count of records for the column value 4 is lesser than the other column values)
Could anyone please let me know why this happens and how to resolve this ?
Thanks in advance,
DBLearner
View 2 Replies
View Related
Jun 1, 2007
Hello All,
I am tackling with unique request. I have to download the ".zip" files from the https and uncompress them. Now, the fetch process works fine when I am downloading the files. Using .net and .IO namespaces from system library.
Similarly after downloading I have to uncompress the files as these files are treated by compressed file folders by windows xp. I know it can be achieved by using the IO.compression class form system namespace.
But the only trouble here is IO.compression supports ".gz" and "Gzipstream.Uncompress" and I wonder how I would be able to get the ".zip" and "Zipstream.uncompress" done.
Thanks a million in all your help and advice. Also I appreciate for your time.
Regards
Sandesh
View 1 Replies
View Related
Jul 26, 2007
SQL Express installation is bombing out with the "A beta version of the .NET Framework 2.0 or SQL Server was detected on the computer...".
From the log file:
Running: PerformSCCAction2 at: 2007/6/26 8:17:27
Loaded DLL:C:WINsystem32msi.dll Version:3.1.4000.2435
Product "{7131646D-CD3C-40F4-97B9-CD9E4E6262EF}" versioned 2.0 is not compatible with current builds of SQL Server.Expected at least version: 2.0.50727.42
The Product Name is ".NET Framework"
Now what is peculiar to me is that
1) this machine should have never had the beta software on it;
2) I have seen people with similar issues except their versioned info always said "versioned 2.0.50727" and the product guid is different, this one only says "2.0"; and
3) I thought that product guid was for the RTM 2.0.50727.42 release!
What is going on here and where is it getting this version information?
brian
View 1 Replies
View Related
Jul 20, 2005
Hello,I have this peculiar problem concerning MS SQL Server.My company works with an mailing application (ASP) which uses SQLServer as it's repository. What I want to do is send data directlyfrom my own application to this SQL Server in order to feed themailing application.To test if this was possible I linked the tables from SQL Server in MSAccess and entered the data. This worked fine and the data was pickedup correctly by the mailing application.The problem occurs when I send the data from my application (Javaapplication with JDBC connection). The data is in this case no longerpicked up by the application. The strange thing is that the data whichis entered through Access and the data from the Application lookidentical in de database view. The problem also occurs when the datais send with the tool winSQL and when I view the data in here it stilllooks identical.Even more strange is when I select the record which is not working inAccess and copy it into a new record (only changing the key) itsuddenly works!Has anyone have an idea how this can be?Thanks in advance,Sander Janssen.
View 3 Replies
View Related
Jan 21, 2008
we're trying to get a better understanding of how RS behaves when parameters are being set. We see quirky behavior that is a little difficult to describe. Right now we assume that if the revolving green circle (with the phrase "Report is being generated" beneath it) doesnt appear, the report really wasnt rendered properly, even if the report region changes.
One peculiarity that seems pretty consistent is on reports we've prototyped with "from" and "to" date parameters. It seems that when we set one date (doesnt matter which is 1st) things progress normally, ie no "report clearing event" occurs as a result of setting cursor focus in the calendar control and changing its value. The report region doesnt change from what showed previously. But trying to set focus on 2nd (doesnt matter if its "from" date or "to" date, just that its the 2nd date being set) always seems to trigger some kind of event that 1) doesnt allow focus to be on that text box, 2) blanks out the report region including headings. Only after this "event" occurs, can we set focus on the 2nd date, change the value and click the "view report" button for rerendering.
We see similar types of behavior with other types of parameters that include multi value dropdowns and booleans. The toughest part of this is trying to explain it to our users. On some parameters, the event always occurs every time they are changed. On other parameters, it appears that the event only occurs if another parameter was changed beforehand.
I believe we've even seen headings with no data rendered, thinking temporarily that no rows were returned, just to find out that by clicking the "view report" button there really was data to be reported based on current filters. Unfortunately I cant reproduce this scenario when I want to.
View 3 Replies
View Related
Apr 27, 2005
Rather than the real code, here's a sample we came up with.
Here's the C# Code:
public class sptest : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Label Label1;
private DataSet dtsData;
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
string strSP = "sp_testOutput";
SqlParameter[] Params = new SqlParameter[2];
Params[0] = new SqlParameter("@Input", "Pudding");
Params[1] = new SqlParameter("@Error_Text", "");
Params[1].Direction = ParameterDirection.Output;
try
{
this.dtsData = SqlHelper.ExecuteDataset(ConfigurationSettings.AppSettings["SIM_DSN"], CommandType.StoredProcedure, strSP, Params);
Label1.Text = Params[0].Value.ToString() + "--Returned Val is" + Params[1].Value.ToString();
}
//catch (System.Data.SqlClient.SqlException ex)
catch (Exception ex)
{
Label1.Text = ex.ToString();
}
}
Here is the stored procedure:
CREATE PROCEDURE [user1122500].[sp_testOutput](@Input nvarchar(76),@Error_Text nvarchar(10) OUTPUT)AS
SET @Error_Text = 'Test'GO
When I run this, it prints up the input variable, but not the output variable.
View 2 Replies
View Related
Sep 12, 2004
1. Use mssql server agent service to take the schedule
2. Use a .NET windows service with timers to call SqlClientConnection
above, which way would be faster and get a better performance?
View 2 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 22, 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 witha particular query. It would take approximately 22 seconds to return100 rows, thats about 0.22 seconds per row. Note: I ran the query insingle user mode. So I tested the query on the Development server bytaking a backup (.dmp) of the database and moving it onto the devserver. I ran the same query and found that it ran in less than asecond.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 isrelated to some external hardware issue like: disk space, memory etc.Or could it be OS software related issues, like service packs, SQLServer configuations 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 systemrelated issue.Any Ideas would help me greatly!Thanks,Brian T
View 2 Replies
View Related
Mar 9, 2007
We have the same application installed on a few different environments with similar servers and similar hardward. The only difference is the versions of SQL and the colations.
Is SQL 2005 a lot faster that SQL 2000? Could colation type make a big effect on performance?
ScAndal
View 1 Replies
View Related
Aug 31, 2007
HiI want to insert 1000s of records into SQL Server 2005 Database with some manipulation. So that i put into the For Loop and inserting record.Inside the loop i am opening the connection and closing after use. The sample code is belowfor(int i=0;i<1000;i++){ sqlCmd.CommandText = "ProcName"; sqlCmd.Connection = sqlCon; sqlCmd.Connection.Open(): sqlCmd.ExecuteNonQuery(); sqlCmd.Connection.Close(); } What my Question is.. How is the Performance of this Code..?? Will is take time to get the Connection and Close the Connection in every itration?Or Shall I Open the Connection in Begining of the outside loop and close the connection at end of the Loop? will it increase the Performace?Please clarify me these question.. Thanks in advance.
View 1 Replies
View Related
Dec 8, 2003
I have a following problem with SQL performance:
this line 'select * from [viewUserLatestFee]' executes instantly (in Query Analiser)
this line 'select * from [viewUserLatestFee] where orgID = 1' takes up to 30 seconds for 1000 rows (still in Query analiser)
can anyone please help - I seem to have ran out of ideas
I have a feeling people might be curious about the view so here it is:
SELECT dbo.viewUserPosition.id, dbo.viewUserPosition.username, dbo.viewUserPosition.password, dbo.viewUserPosition.title,
dbo.viewUserPosition.firstName, dbo.viewUserPosition.lastName, dbo.viewUserPosition.email, dbo.viewUserPosition.address1,
dbo.viewUserPosition.address2, dbo.viewUserPosition.suburb, dbo.viewUserPosition.postcode, dbo.viewUserPosition.country,
dbo.viewUserPosition.state, dbo.viewUserPosition.mailAddress1, dbo.viewUserPosition.mailAddress2, dbo.viewUserPosition.mailSuburb,
dbo.viewUserPosition.mailPostcode, dbo.viewUserPosition.mailCountry, dbo.viewUserPosition.mailState, dbo.viewUserPosition.birthDate,
dbo.viewUserPosition.joinDate, dbo.viewUserPosition.lastUpdated, dbo.viewUserPosition.orgID, dbo.viewUserPosition.positionID,
dbo.viewLatestPaidFee.feeID, dbo.viewLatestPaidFee.mshipID, dbo.viewLatestPaidFee.name, dbo.viewLatestPaidFee.[desc],
dbo.viewLatestPaidFee.terms, dbo.viewLatestPaidFee.period, dbo.viewLatestPaidFee.periodType, dbo.viewLatestPaidFee.fee,
dbo.viewLatestPaidFee.startDate, dbo.viewLatestPaidFee.endDate, dbo.viewLatestPaidFee.deleted, dbo.viewLatestPaidFee.feePaidID,
dbo.viewLatestPaidFee.paidDate, dbo.viewLatestPaidFee.effectiveDate, dbo.viewLatestPaidFee.approved, dbo.viewLatestPaidFee.optionID,
dbo.viewLatestPaidFee.paidAmount, dbo.viewLatestPaidFee.feePaidEndDate
FROM dbo.viewUserPosition LEFT OUTER JOIN
dbo.viewLatestPaidFee ON dbo.viewUserPosition.id = dbo.viewLatestPaidFee.userID
Here is viewUserPosition:
SELECT dbo.tblUser.id, dbo.tblUser.username, dbo.tblUser.password, dbo.tblUser.title, dbo.tblUser.firstName, dbo.tblUser.lastName, dbo.tblUser.email,
dbo.tblUser.address1, dbo.tblUser.address2, dbo.tblUser.suburb, dbo.tblUser.postcode, dbo.tblUser.country, dbo.tblUser.state,
dbo.tblUser.mailAddress1, dbo.tblUser.mailAddress2, dbo.tblUser.mailSuburb, dbo.tblUser.mailPostcode, dbo.tblUser.mailCountry,
dbo.tblUser.mailState, dbo.tblUser.birthDate, dbo.tblUser.joinDate, dbo.tblUser.lastUpdated, dbo.tblRelPosition.orgID,
dbo.tblRelPosition.positionID
FROM dbo.tblUser INNER JOIN
dbo.tblRelPosition ON dbo.tblUser.id = dbo.tblRelPosition.userID
and viewLatestPaidFee:
SELECT dbo.tblMshipFee.id AS feeID, dbo.tblMshipFee.mshipID, dbo.tblMshipFee.name, dbo.tblMshipFee.[desc], dbo.tblMshipFee.terms,
dbo.tblMshipFee.period, dbo.tblMshipFee.periodType, dbo.tblMshipFee.fee, dbo.tblMshipFee.startDate, dbo.tblMshipFee.endDate,
dbo.tblMshipFee.deleted, fp.id AS feePaidID, fp.paidDate, fp.effectiveDate, fp.approved, fp.optionID, fp.paidAmount, fp.endDate AS feePaidEndDate,
fp.userID
FROM dbo.tblRelMshipFeePaid fp INNER JOIN
dbo.tblMshipFee ON dbo.tblMshipFee.id = fp.feeID AND fp.endDate =
(SELECT MAX(fp2.[endDate])
FROM [dbo].[tblRelMshipFeePaid] fp2
WHERE fp2.[userID] = fp.[userID])
View 4 Replies
View Related
Jan 13, 2005
We used a stored proc to pull totals from a database. Everything was fine until the table grew and started to time out. So we created a temp table to populate with a range of data and then pull the totals from there. Everything was fine until the table grew and started to time out. Any suggestion?
View 3 Replies
View Related
Jan 17, 2002
Hi,
I am newly joined as SQL DBA. I want to check the Physical disk Performance. we have RAID 5 with 5+1 disks. I calculated NO Of IO's Per Disk. But how do we know what is actual limit of IO's per disk.
Thanks
Praveen
View 1 Replies
View Related
May 8, 2001
What's my best bet in getting better performance out of one of my database servers? Currently we have 1 set of Raid5 disks partitioned into 2 drives. This houses everything (system, database, and logs) If that server has 2 slots left for drives I was thinking of putting 2 mirrored drives and getting the logs off the main database space? (Make sense?) This is a vendored application so working with new indexes etc. isn't something I should do wo/ the vendor's interaction. Will what I describe above help?
Thanks
View 2 Replies
View Related
Mar 31, 2001
hi,
i am using to move data from oracle to oracle.
i have used stored procedure in oracle for the update/insert .
the dts calls the stored procedure for each record, due to this the performance has gone down. how do i increase the speed of data xfer.
has any one done any thing similar ?
Tushar
View 1 Replies
View Related
Jun 26, 2001
We have SQL Server running on a dual processor Pentium 500mhz server. Our database is hit by about 300 users. 200 of those users are doing constant searches though a client table of about 250,000 records, which in turn is linked to a history table containing over 5,000,000 records. This is only the tip of the iceberg, we have many triggers, procedures, updates, etc. going in the background. The database has over 500 tables.
Keep in mind, these searches that are taking place can involve all kinds of fields: phone number, company name, fax number, first name, last name, status, wildcard searches, etc. So as you can imagine, the database is being hit with all kinds of funky requests to find records. I will be the first to admit that our developers (vendor) are not the best code writers, and we have a tough time getting them to optimize something they do not even understand themselves.
As I speak, our processor utilization is maxing out between 95 to 100 percent. I've done a lot of performance tuning and all of the problems lie in the searching. We've built, tested, rebuilt, re-tested each and every index. I even used the Profiler to filter what I could. It has improved, but our database is growing at a rate of 10 megs a day (already close to 3 gigs, not that huge). I think I've optimized my indexes as best as I can considering all the fields and possibilities available to users to search for records.
For a database that requires all of these different search criteria, what would be a more optimal server? We are looking to purchase something ASAP. I could really use help from someone in a similar situation. It seems odd, in mind, that a company of 300 people would need to rely on a quad server (four processor capability.).
Thanks. JT
View 3 Replies
View Related
May 31, 2000
HI
I have 700 to 900 mb of production database , 2 gb of ram , 30 gb hard disk,
My production machine is runnng very slow , i have check everything memory,
page/sec, catch hit ratin , dbcc dbreindex but still it performance is not up to the mark.
If i stop SQL SERVER & restart for few days machine works fine but after that
again same thing it work very slow, what could be the reason
if any one had any solution please suggest.
Thanks
Nil
View 2 Replies
View Related
Jan 17, 2000
Hi friends,
My company has aution web site, it is written in Java and all sql statements generated dynamically. No stored procedures used. If 30 users uses this site it is OK but if around 300 users uses then the site becomes very slow(almost dead) and developers saying that database is the bottle neck. Please help me in this problem how can I check and overcome this problem.
Thanks
dindu
View 2 Replies
View Related
Apr 27, 2000
I am running a SQL 7.0 server on a two processor machine. We are having some performance issues.
one of the processor is always above 90% utilization but the second is barely at 50%.
Will adding another processor help or are the processes locked to one processor.
The server is a dedicated sql server. nothing else is running on it.
Thanks for any info you can provide.
Pierre
View 2 Replies
View Related
Oct 20, 1999
Hi,
What I have to do to determine which is the capacity (transactions / sec) of MS SQL Server 7.0 on a specific hardware configuration?
Thank you,
Sebastian Bologescu
View 1 Replies
View Related
May 5, 2001
We have recently upgraded to SQL 7.0 on NT 4.0/sp6 box which has got 4 PIII 700 processors, 1GB RAM, and 70GB HDD on RAID 1 and RAID 5. We feel that the application performance is not great as expected in SS7. (The application was running in 6.5 smoothly and performance was good)
Is there any option needs to set to improve performance? Now, SS 7 using all the 4 processors and dynamically allocated memory, etc. Any thoughts greatly appreciated.
Thanks in Advance
Jaya
View 2 Replies
View Related
Mar 14, 2002
I'm running MS SQL Server on a 1.4 GHz AMD Athlon Processor with 750 MB or RAM and ample disk space. I have a table with 14 columns; 2 datetime, 8 int and the rest are varchar of various sizes less than 13.
I run a java process on another machine that connects to the database and insert records. It takes about 6 minutes to insert 100,000 records.
I run the xp performance monitor and only about 25% of the SQL Server machine's cpu is being used. I run top on the Linux box running java and I see about the same results. Neither machine is kept busy processing. Why don't I get better performance? Could my local area network be that slow? How many inserts per minutes is good performance?
Thanks for your input.
View 1 Replies
View Related
Jan 23, 2001
Does anyone know the performance differences between returning data from SQL Server as XML vs. as a record set? We are about to dive into the For XML world full force, but we wanted to make sure that we are not heading for a performance nightmare.
Thanks for any insight on this. I'll try to look for white papers and do some testing in the meantime.
View 3 Replies
View Related
Feb 5, 2004
I ave the following Code in my Stored procedure.
Declare Cursor for table A
WHILE @@FETCH_STATUS = 0
Get values from other function based on some business logic.
INSERT Into another table B
(or)
UPDATE to another table B
END
I have to insert/update values to table B, one by one row. So, it is taking more time.
Is there any way to collect the values into a temporary storage and Insert/update or Move the values to table B.
View 11 Replies
View Related
Apr 4, 2008
1. where do we see the buffer cache hit ratio. can we set the buffer catche hit ratio manually.
2.In query execution plan we execute the query for performance issue.which parameters we check to take an action?
View 4 Replies
View Related
Apr 14, 2008
I have a small doubt. If we keep our data files and log files on sepertate disks how this can improve the database performance.
View 2 Replies
View Related
Apr 5, 2006
Hello,
I build a query in SQL-server 2000 but i'm not happy with the performance, it takes about 15 minutes to execute the query (4 min INSERT and 11 min UPDATE). The table tbl_total has 3 million records and an index on Contract and Item, the table contracts has 1 million records and a key on Contract and Item.
How can I speed up this query, is it for example possible to put an index on @table (internal table)?
Thanx in advance!
DECLARE @table TABLE (Contract nvarchar(15), Item nvarchar(12), Change_date datetime)
INSERT INTO @table
SELECT TOT.Contract, TOT.Item, MAX(TOT.Change_date)
FROM tbl_total TOT
WHERE EXISTS (SELECT 'X' FROM contracts CONT
WHERE TOT.Contract = CONT.Contract
AND TOT.Item = CONT.Item)
GROUP BY TOT.Contract, TOT.Item
UPDATE contracts
SET contracts.Change_date = TT.Change_date
FROM contracts INNER JOIN @table TT On
contracts.Contract = TT.Contract AND
contracts.Item = TT.Item
View 1 Replies
View Related
Dec 4, 2006
Hi
I wanted to find out which is faster in terms of performance:
e.g.
select * from orders where orderRef = '00093'
Or
select * from orders where orderRef like '00093'
I know there is a differnece if i use the wild cards % etc in the results but i wanted to find out with regards to the queries above?
View 13 Replies
View Related
Jan 20, 2007
For performance should we index on primary key & data in table in the same file group or different file group (same or different drive) ?
Thanks,
Andy
View 2 Replies
View Related
Aug 23, 2007
i need help in gaining the performance of this query
SELECT
tblSuperClientFile.ClientRefNo,
tblReferral.RefID,
tblRail.RailDescr,
tblReferral.SuperClientVendorID,
tblVendor.VendorName AS Client,
tblReferral.AssignedVendorID,
tblReferral.ReferralDate,
tblSpikeDate.DateCompleted AS PlanRevCompleted,
tblReferral.CloseDate,
tblCloseReason.CloseReason,
tblBankruptcyInfo.BK_Filing_State,
tblBankruptcyInfo.BK_Case_Number
INTO #PlanRev
FROM FNFBSDataMart.dbo.tblSpikeDate tblSpikeDate WITH (NOLOCK)
INNER JOIN #ActiveBK
ON tblSpikeDate.MasterID = #ActiveBK.MasterID
AND tblSpikeDate.FID = 3160
AND tblSpikeDate.DateCompleted <= GetDate()-5
INNER JOIN FNFBSDataMart.dbo.tblReferral tblReferral WITH (NOLOCK)
ON tblReferral.RefID = tblSpikeDate.RefID
AND tblReferral.ReferralDate >= GetDate()-180
AND tblReferral.AssignedVendorID NOT IN (188,1721)
INNER JOIN FNFBSDataMart.dbo.tblBankruptcyInfo tblBankruptcyInfo WITH (NOLOCK)
ON tblReferral.RefID = tblBankruptcyInfo.RefID
AND #ActiveBK.bk_Case_Number = tblBankruptcyInfo.bk_Case_Number
INNER JOIN FNFBSDataMart.dbo.tblSuperClientFile tblSuperClientFile WITH (NOLOCK)
ON tblReferral.ClientFileID = tblSuperClientFile.ClientFileID
AND tblSuperClientFile.SuperClientVendorID IN (1816,125,127,1706,766,1820,137,141,144,145,1593,1808,146,990,1745,149,1215,1854,1867)
INNER JOIN FNFBSDataMart.dbo.tblRail tblRail WITH (NOLOCK)
ON tblReferral.RailID = tblRail.RailID
INNER JOIN FNFBSDataMart.dbo.tblVendor tblVendor WITH (NOLOCK)
ON tblReferral.SuperClientVendorID = tblVendor.VendorID
INNER JOIN FNFBSDataMart.dbo.tlkpState tlkpState WITH (NOLOCK)
ON tblSuperClientFile.StateID = tlkpState.StateID
AND (tblSuperClientFile.SuperClientVendorID <> 1820
OR tlkpState.Abbrev NOT IN ('AZ','AK','CA','HI','ID','NV','OR','TX','UT','WA'))
LEFT OUTER JOIN FNFBSDataMart.dbo.tblCloseReason tblCloseReason WITH (NOLOCK)
ON tblReferral.CloseReaID = tblCloseReason.CloseReaID
can anyone have a look at it and give me a feed back asap
View 1 Replies
View Related
