Effect Of Joins On The Speed
Aug 6, 2007
Hi..
I want to know that when I do something like..
Select Query
Left Join
Select Query 2
Left Join
Select Query 3
How does it work actually?
As in, whether Query 2 & Query 3 will work only on the records retrieved by Query 1 only.
Or, all the select statements retrieves all the records and then the condition is applied to filter out the results.
Also, does the order of the Select statements make any difference on the speed?
Thanks
View 4 Replies
ADVERTISEMENT
Aug 1, 2007
I am joining around 8 tables in my query. All the joins are inner joins on the primary - foreign key. 3 tables have millions of rows and 2 tables have hundreds of thousands rows and the remaining have less than 100 rows.
The where clause in my query has 2 fields (separate tables) . Both the fields are indexed (non clustered).
The query runs very slow. It takes 11 seconds to return the output which conists of only 10 rows (Top 10) .
What can I do to speed the query?
View 4 Replies
View Related
Apr 28, 2008
Hi,
The following query should return a list of clr_id's that have a match in at least 1 of the other fields mentioned in the joins.
declare @keyWord varchar(40)
set @keyWord = 'merc'
set NOCOUNT on
SELECT distinct clr.clr_id
FROM CLR
LEFT OUTER JOIN CO ON CLR.CO_ID = CO.CO_ID
LEFT OUTER JOIN CLR_NM ON CLR.CLR_ID = CLR_NM.CLR_ID
LEFT OUTER JOIN CLR_USE_YR ON CLR.CLR_ID = CLR_USE_YR.CLR_ID
LEFT OUTER JOIN MODL ON CLR_USE_YR.MODL_ID = MODL.MODL_ID
LEFT OUTER JOIN PAINT_CD ON CLR.CLR_ID = PAINT_CD.CLR_ID
WHERE co.long_nm like '%'+@keyWord+'%'
OR clr_nm.clr_nm like '%'+@keyWord+'%'
OR clr_use_yr.yr_num like '%'+@keyWord+'%'
OR paint_cd.paint_cd like '%'+@keyWord+'%'
OR modl.modl_nm like '%'+@keyWord+'%'
The query runs at 3secs.
Could I improve the query somehow?
I was thinking that, since I actually need a distinct set of clr_id's, I should somehow check only the clr_id's that don't have a match in any of the previous joins.
View 19 Replies
View Related
Oct 18, 2007
Hi,
I have several data bases on a server (SQL Server 2000 only, no web server installed) and lately, as the company keeps gowing, my users complain saying the server gets slow, (this dbs are well designed and recieve optimizations and integrity checks, etc) because of this, Im thinking about getting a new server to repleace my old ProLiant ML 330 which was bought 4 years ago but Im concerned about what server arquitecture or characteristic can help me best to improve response performance, is it HD speed? Processor speed? or more Ram? I want to make a good decision, so I´d really appreciate your help...
Thanks, Luis Luevano
View 1 Replies
View Related
Nov 3, 2000
We find that a delete command on a table where the rows to be deleted involve an inner join between the table and a view formed with an outer join sometimes works, sometimes gives error 625.
If the delete is recoded to use the join key word instead of the = sign
then it alway gives error 4425.
625 21 0 Could not retrieve row from logical page %S_PGID by RID because the entry in the offset table (%d) for that RID (%d) is less than or equal to 0. 1033
4425 16 0 Cannot specify outer join operators in a query containing joined tables. View '%.*ls' contains outer join operators.
The delete with a correleted sub query instead of a join works.
Error 4425 text would imply that joins with view formed by outer joins should be avoided.
Any ideas on the principles involved here.
View 1 Replies
View Related
Aug 11, 2005
SQL Server 2000Howdy All.Is it going to be faster to join several tables together and thenselect what I need from the set or is it more efficient to select onlythose columns I need in each of the tables and then join them together?The joins are all Integer primary keys and the tables are all about thesame.I need the fastest most efficient method to extract the data as thisquery is one of the most used in the system.Thanks,Craig
View 3 Replies
View Related
Mar 13, 2008
I have a sqlserve, which service account is 'local system', running in machine A.
A credential ,which associated a windows user U1 in machine A, mapped to a sqlserver login Login1.
A file named 1.txt that only can be accessed by U1 in machine A.
A CLR procedure P1 that would read the 1.txt file.
I encounter a error when i access the 1.txt file through P1 as Login1:
A .NET Framework error occurred during execution of user-defined routine or aggregate "HelloWorld":
System.UnauthorizedAccessException: Access to the path 'E:1.txt' is denied.
System.UnauthorizedAccessException:
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options)
at System.IO.StreamReader..ctor(String path, Encoding encoding, Boolean detectEncodingFromByteOrderMarks, Int32 bufferSize)
at System.IO.StreamReader..ctor(String path)
at StoredProcedures.StoredProcedure1(SqlString fileName)
.
I think it need a credential when a sqlserver login access some resource outside the sqlserver, so i add a credential and mapped to the login
any suggestions would be appreciated.
And please correct me if i have any inaccurate concept.
View 9 Replies
View Related
Nov 12, 2006
I have this stored procedure. I want to run a few simple SQL functions against my tables. In particular I want to take a subset of records (One or Two years worth) and calculate AVG, VAR and STDEV.
It does not work the way I thought it would. I end up with the whole input table in #tempor1 which is about 6 years worth of records.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[findAve1YearDailyClose_MSFT]
AS
BEGIN
SET NOCOUNT ON;
SELECT adjClosed, volume INTO #tempor1 FROM dbo.dailyCl_MSFT
GROUP BY dateTimed, adjClosed, volume
HAVING (dateTimed > DATEADD (year, -1, MAX (dateTimed)))
SELECT AVG (adjClosed) AS "AVGAdjClose1Year",
VAR (adjClosed) AS "VARAdjClose1Year", AVG (volume) AS "AVGVolume1Year",
STDEV (volume) AS "STDEVVolume1Year", COUNT (*) AS "total"
FROM #tempor1
END
Thus if I change the number of years I subtract from the latest date from 1 to 2 I end up with the same result. What is the problem?
Thanks.
View 3 Replies
View Related
May 23, 2001
When running a trace on a database, how does if affect that databases performance? Does it slow it down at all?
Thanks In Advance,
Chris
View 1 Replies
View Related
Feb 4, 2004
Hello,
What happens to these REPLication agents if SQL Agent is stopped and started:
Snapshot Agents
Merge Agents
Misc Maintenance Agents
Can the agent be stopped and started with no thought to the status of replication, or should the replication state be modified in some way before any change to the status of the Agent?
(I know REPL depends on agent, so wuestion is, can REPL simply resume or not when agent is re-started.)
Thanks.
MichaelGG
View 2 Replies
View Related
Jan 31, 2004
If the server is upgraded to Windows 2000, will SQL 6.5 require to be upgraded to SQL 7 or 2000?
View 1 Replies
View Related
Apr 30, 2007
Does someone know if doing a reindex on a clustered or non-clustered index cause the snapshot file to grow? In other words, is the data that makes up the snapshot copied from the source to the snapshot database? If a normal reindex is done on the underlying database, will it block users from acessing the snapshot? Any help would be appreciated.
View 1 Replies
View Related
Jul 7, 2006
How can I achieve the same effect as a cross join (since the merge operator doesn't have a cross join)?
Situation is this... a flat file has some header and footer information that I need to keep and attach to each row. So for simplicity sake of an example lets just say header has only 1 thing we care about - a row that says DATE=01/01/06.
I take the file and run a split to split into "Date" "Data" and "other" (other has all the throwaway rows in header and footer I don't care about). Then I use a derived column object to get all the columns out of the "Data". Finally I want to add that Date metadata back to every row in the data...
I thought this would be an easy thing to do.. but I can't seem to figure out how to duplicate that Date info into every row.. Hopefully I am overlooking something simple.
Thanks in advance.
View 13 Replies
View Related
Dec 7, 2004
We have a database that we have designed so all the data dependances are managed by the front end code. However the company we are writing it for has asked us to add around 50 constraints. I was just a little worried what kind of effect on performance this would have. Cheers Ed
View 1 Replies
View Related
Nov 16, 2006
Edit: Sorry This is OSQL.What I use as my query is:"DELETE FROM timerecord WHERE Actual_Time_In LIKE '11.12.2006%'"The row of Actual_Time_In is formatted with Date and time (MM.DD.YYYY HH:MM:SS) sometimes there are ten records and I'd rather not have to remove them from the table one at a time. However, even though I have a record that is '11.12.2006 22:43:00' my delete doesn't work osql states I have 0 rows affected.This is only MSDE so I don't have anyother way to open the table.Sometimes these records have other records that reference them. Is there anyway to do a cascading delete without it getting to complex?Thanks of all your help, I am just a tech support guy beating his head against a wall..
View 2 Replies
View Related
Feb 5, 2007
Hi all,
Does JOIN order effect efficiency?
If I have three large tables to join together should I join the two that I know will cut the number of rows down a lot first and then join the 3rd table or does it make no difference (if I join the first and 3rd - which I know will be a large result set and then join the 2nd).
Thanks in advance,
Chiz.
View 1 Replies
View Related
May 1, 2008
Hello all, thanks in advance for any advice here.
My question is, what's the effect of the Enterprise Manager > Tasks > Shrink Database function? It seems to reduce the used space on the device. Testing it on some dev machines, I've seem to have gotten back as much as 20g of space. I know it should grow back to that, but the time it took was minimal, and it didn't seem to affect my developers. They tend to add alot and delete alot during testing. What negative effects of running this should I look out for? Will it affect the DB long term? Is is preferable to schedule once a month or so? Is this done on Production DB's?
Thanks for any guidence on the usage of this.
Bob
Not a downstroke, fistpicker
View 6 Replies
View Related
May 27, 2008
Hi Guys,
I have created a report where I have Z-Index all set correctly, but I cannot see the desired effect of toggling as can be seen in the AdventureWorks sample of SalesOrder report that gets shipped with SSRS. What am I missing?
View 1 Replies
View Related
Jul 13, 2007
I'm looking into the automatic recompilation of stored procedures andI have been reading up on the "Do not recompute statistics" option onindexes.Am I correct in concluding that disabling the "Do not recomputestatistics" option for an index, will ensure that no automaticrecompilations will occur as a result of updates to data in thatindex?Am I also correct in understanding that the "Update Statistics" willstill update statistics for the index even if the "Do not recomputestatistics" option is disabled?RegardsBjørn
View 1 Replies
View Related
Jul 20, 2005
Hello All,I have a stored procedure which will act like a main/controller scriptwhich will then invoke more stored procedures (~20). Basically, itlooks something like below:-- start scriptcreate procedure ...print 'process started'exec sp_1exec sp_2exec sp_3....print 'process ended'-- end scriptLooking at it, after running that procedure, immediately I would expectthe first PRINT statement to be printed but it won't. It seemedthat the print statements would only display the messages afterthe whole processing completed.What's the reason for this sort of behaviour. If so, then we wouldnot be able to print any progress reporting in our scripts.Please comment.Thanks in advance.
View 4 Replies
View Related
Jul 20, 2005
Hello,When I try to run the following query on two different SQL Servers,I get error on one of the server (both has same set of data). I wastrying to get rows for ProductCode='XYZ_Q1'.SELECT ProductName, ProductType, ProductDesc FROM Product WHEREDepartmentID=12 AND ProductType > 2000 AND CAST(SUBSTRING(ProductCode,CHARINDEX('_', ProductCode)+2, 1) AS int)=1Example dataProductCode|ProductName|ProductType|DepartmentID|P roductDescXYZ_T_1|Test1|1000|12|TestXYZ_T_2|Test2|1000|12|TestABC_T_1|Test3|1000|11|TestABC_T_2|Test4|1000||11|TestXYZ_Q1|Test5|1000|12|TestABC_Q1|Test6|1000|11|TestIt's trying to cast all values under 'ProductCode' column instead ofapplying to subset with condition 'DeparmentID=12 AND ProductType >2000 'I solved the problem by equating it to whole string rather thantrying to extract the integer part of it. But I wanted find-out reasonas to why this is happenning.Is there any SQL Server setting that's causing this?Thank you very muchManchaiah
View 1 Replies
View Related
Sep 11, 2006
Hi,
I'm trying to grant/deny object permissions in a user database using Enterprise Manager and query tool without success (for €˜public€™ role and individual sql logins). I€™m not getting any error messages. Permission changes just don€™t take effect. Although, there are few objects, which already have permissions granted and I'm able to change permissions for these ones.
How can I change the permissions?
View 4 Replies
View Related
Nov 6, 2006
Hi, all experts here,
Thank you very much for your kind attention.
I am encountering a problem with reports built on my SQL Server 2005 Reporting Services Server. The reports parameters do not take effect at all? I chose the value from the report parameter list, but the result returned the whole report data. Why is that? And how can I solve this problem? Really need help.
Thanks a lot in advance for any guidance and help.
With best regards,
Yours sincerely,
View 11 Replies
View Related
Sep 11, 2006
Dear all,
I have some questions regard subject above. I want to know the impact of replication process after SA pasword been changed.
Example: server1 (hq), server2 (district) & server3 (district).
If server1, SA password been changed, is it server2 & server3 need to change too?
may i know where is it the replication SA password stored? is it replication script refer ID (SA) from Security -> Logins?
I hope someone can help me.. thanks.
View 1 Replies
View Related
Aug 10, 2007
I just want to change the length of 2 fields. They're CHAR and NVARCHAR type. I want to change the length from 50 to 75.
Will doing this effect the data? Will it delete the data in those columns? I just want to make sure before I do anything. The table contains about 2.5 million rows, so I don't want to mess anything up. I'm aware that the script may take a while to run and finish, but I'm only concerned about data integrity.
Here is my example script syntax to show what I'm planning to do:
Code Snippet
alter table credit alter column writeoffreason char(75) not null;
Does it matter what type of data is in the columns? For now, it's only CHAR and NVARCHAR.
I also read somewhere that you can increase length but cannot decrease without repercussions?
Thanks!
View 3 Replies
View Related
Jan 3, 2008
I build reports using reporting services 2005 sp2.
I have 3 parameters---> 3 multi value combo box .
However when I tick "allow blank value" within the report parameter
properties for this drop menu, report WILL NOT run without making
selection.(The xml is ok allow blank value set to true)
the reports still require you to enter a value for all parameters in the report when the report can and should be able to be run with no parameter defined.
the trick "default values for all of the params to "=String.Empty"" is not good .
I found this article but i can't find microsoft hotfix for it .
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125877
"Thank you for filing the bug. This issue has been fixed for the next CTP.
Posted by Microsoft on 8/11/2005 at 7:00 PM"
Am I missing something obvious here?
View 1 Replies
View Related
Jun 13, 2001
Hi
I had run a stored procedure in my server that update statistics against all user defined tables in my database (MSSQL 7.0).
Since then I am getting errors in my ASP application where I am reffering to adovbs.inc.
Here is an example of errors I get.
Microsoft VBScript runtime error '800a0411'
Name redefined: 'adOpenForwardOnly'
/Essai/adovbs.inc, line 14
Below is the stored procedure I have run against the database.
Can anybody help tank you guys.
CREATE PROCEDURE update_all_stats
AS
/*
This PROCEDURE will run UPDATE STATISTICS against
ALL user-defined tables within this database.
*/
DECLARE @tablename varchar(30)
DECLARE @tablename_header varchar(75)
DECLARE tnames_cursor CURSOR FOR SELECT name FROM sysobjects
WHERE type = 'U'
OPEN tnames_cursor
FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @tablename_header = "Updating " +
RTRIM(UPPER(@tablename))
PRINT @tablename_header
EXEC ("UPDATE STATISTICS " + @tablename )
END
FETCH NEXT FROM tnames_cursor INTO @tablename
END
PRINT " "
PRINT " "
SELECT @tablename_header = "************* NO MORE TABLES" +
" *************"
PRINT @tablename_header
PRINT " "
PRINT "Statistics have been updated FOR ALL tables."
DEALLOCATE tnames_cursor
View 1 Replies
View Related
Feb 23, 2004
Hi All,
I have a view that does some calculations on data in the database. Note in the two code snippets, that the columns being used in the calculations are all type FLOAT(53).
My confusion stems from the fact that both code snippets should be functionally the same in my "view" (*snicker* I kill me...), but they return different results which I think are related to rounding issues.
The first snippet:
CREATE VIEW dbo.VIEW_Calculate_PortfolioIndex
AS
SELECT PP.PortfolioID AS PortfolioID,
PP.CreateDate AS CreateDate,
(ROUND((PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100.00)), 2) * PP.AvgHighPriceRatio) AS HighIndex,
CASE WHEN PPI.CloseIndex IS NULL
THEN 100.00
ELSE ROUND((PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100.00)), 2)
END AS CloseIndex,
(ROUND((PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100.00)), 2) * PP.AvgLowPriceRatio) AS LowIndex,
PP.Volume as Volume
FROM dbo.PortfolioIndex PPI INNER JOIN
dbo.PortfolioPerformance PP ON
PPI.PortfolioID = PP.PortfolioID AND
PPI.CreateDate = PP.PrevDate
GO
and it's result set:
PortfolioIDCreateDateHighIndexCloseIndexLowIndexVolume
102/20/2004781.11774.17769.53527896
112/20/2004757.97750.36742.93605740
122/20/2004509.92501.72494.854180516
132/20/2004988.23980.65973.58632337
142/20/20041283.261269.571259.37416145
And the second snippet:
CREATE VIEW dbo.VIEW_Calculate_PortfolioIndex
AS
SELECT PP.PortfolioID AS PortfolioID,
PP.CreateDate AS CreateDate,
(CloseIndex * PP.AvgHighPriceRatio) AS HighIndex,
CASE WHEN PPI.CloseIndex IS NULL
THEN 100.00
ELSE ROUND((PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100.00)), 2)
END AS CloseIndex,
(CloseIndex * PP.AvgLowPriceRatio) AS LowIndex,
PP.Volume as Volume
FROM dbo.PortfolioIndex PPI INNER JOIN
dbo.PortfolioPerformance PP ON
PPI.PortfolioID = PP.PortfolioID AND
PPI.CreateDate = PP.PrevDate
which returns a different result set:
PortfolioIDCreateDateHighIndexCloseIndexLowIndexVolume
102/20/2004784.52774.17772.89527896
112/20/2004755.64750.36740.64605740
122/20/2004512.43501.72497.294180516
132/20/2004989.77980.65975.1632337
142/20/20041285.991269.571262.05416145
Specifically, I am concerned with the HighIndex and LowIndex values...since the only modification between the two code snippets is that in the second one, the HighIndex and LowIndex calculations use the column name of CloseIndex (as calculated in the select) in the calcs for those two columns, rather than repeating the code used to calculate the CloseIndex column's value.
I am confused as to why the results of the HighIndex and LowIndex caculations are different in the two selects, when the only change (in my view/expectations) is that one references the CloseIndex column, and the other one just reproduces the calculation itself
*scratching head*
any thoughts? Thanks,
Paul
View 14 Replies
View Related
Apr 25, 2007
Hi,
I am trying to use a very easy and simple feature of a reportmodel, model item security.
In my example i have two users; HGHJohn and HGHJKooi
I want to test if I am able to restrict access in the model to a whole entity. HGHJKooi shouldn't be able to see the entity 'Customers'.
These are the steps I executed:
1. In Sqlserver management studio I opened the properties of my model and navigated to the tabpage 'model item security'.
2. I activated the option 'secure individual model items...'
3. In the root of the model I declared two users(groups) as specified above
4. Automatically all nodes inherit these settings from the root.
5. For the entity 'Relations' I change the default, by selecting 'use these roles for each group or user account'
6. I removed HGHJKooi from this list, leaving only 'HGHJohn as model item browser
What I expected at this moment is that when I login the system as HGHJKooi, then I won't see this entity, but I still can! Does anybody know a solution to this problem?
Julian Kooiker
View 1 Replies
View Related
Apr 7, 2008
A report contains images rendered at 300 DPI. This DPI is fixed, in order to achieve the required quality of the image when printed.
Rendering the report (locally, using LocalReport.Render) as a PDF, with the DPI set to 300 in the DeviceInfo string does not appear to have any effect as the images in the resulting PDF are rendered at 96 DPI.
One solution is to render the report as a TIFF, which does have the desired effect. However, it is a PDF that is required, and converting the TIFF to PDF is problematic in itself. In addition the conversion of the TIFF to a PDF (using PDFCreator) results in a black that is now a blue-black instead of true black.
Any suggestions?
View 4 Replies
View Related
Jul 25, 2007
Hi all,
I have a few stored procedures which all perfom SELECT queries on a table in the database. Do these kind of stored procedures affect any other processes or procedures working on that table. I am talking about locks, blocks etc.
For example, the database has a table which gets updated periodically by some process which I don't know. Now I wrote some stored procedures just to do the SQL SELECT with some conditions in WHERE clause. Is there any possibility that my stored procedure failed and the because of this, the process that runs on the table was not executed?
View 4 Replies
View Related
Feb 1, 2007
I'm a newbie to Replication and recently setup the following.
Publisher and Distributor on the same SQL2005 server, then I've got 7 subscribers(SQL2000 servers) and I'm using push subscriptions. I'm replicating 5 SQl tables which don't have too many changes and these are scheduled to run every 3 hours. In a few days a large one off SQL update with add an additional 10,000 rows to one of the replicated tables. I was wondering what impact this would have on the above setup i.e are there any sort of limitations here. I'm assuming not but thought I would check. I'm thinking it will just cause additional overhead on the server, but the update is being applied when no users will be using the database.
Any feedback greatly appreciated.
Thanks
View 1 Replies
View Related
Jul 18, 2007
[Microsoft follow-up]
Hello,
I think I've found a bug.
I have an expression set on the 'Disable' property of a data-flow task. When the expression evaluates to true the data-flow still executes. That seems wrong to me.
I ahve a demo package here: http://folders.live.com/self.aspx/VQ9oHa1TJjc/Public/SSIS%20Stuff/ExamplePackages/20070717%20Expression%20on%20Disable%20property%20has%20no%20effect/Package1.zip
Please reply letting me know whether this is indeed a bug or not.
I'm on SP1 by the way.
Regards
Jamie
View 9 Replies
View Related
