Opinion/Standard For Join Condition

Jul 30, 2007

Hi,

I was having a conversation with another developer about the order that the join conditions are placed. More specifically, about the order the tables are referenced in the join condition. There are two ways to do it, for example:-- Here the table that is referenced first was "declared" first.
SELECT*
FROM
Logon
LEFT OUTER JOIN
Thread
ON Logon.LogonID = Thread.LogonID
LEFT OUTER JOIN
Message
ON Thread.ThreadID = Message.ThreadID

-- Here the table that is referenced first is the table being joined directly above it.
SELECT*
FROM
Logon
LEFT OUTER JOIN
Thread
ON Thread.LogonID = Logon.LogonID
LEFT OUTER JOIN
Message
ON Message.ThreadID = Thread.ThreadID
I realize this is not that big of a deal, but I was wondering if anyone had a good/valid reason for doing one versus the other.

Just curious,

-Ryan

View 10 Replies


ADVERTISEMENT

Join Opinion

Jan 7, 2004

What is the better practice when joining tables in a query example 1 or 2? I've always joined tables together like example 2, but it seems that many people are advocates of example 1. Are there any differences performance wise between the two?

Example 1:
SELECT *
FROM authors AS a INNER JOIN publishers AS p
ON a.city = p.city
ORDER BY a.au_lname DESC

Example 2:
SELECT *
FROM authors AS a, publishers AS p
WHERE a.city = p.city
ORDER BY a.au_lname DESC

View 9 Replies View Related

Performance Between Standard Join And Inner Join

Jun 28, 2007

Hello, everyoneI have one question about the standard join and inner join, which oneis faster and more reliable? Can you recommend me to use? Please,explain me...ThanksChamnap

View 12 Replies View Related

IF Condition In Join??

Jul 20, 2005

Dear GroupI'd be grateful if you can send me on the right track in achieving this.I have three tables A,B,C outlined as follows:Table: AField: RowIDField: EntityIDField: TypeIdentifierTable: BField: RowIDField: NameTable: CField: RowIDField: NameLet's assume I've the following records:Table A:1,1,02,1,1Table B:1,SmithTable C:1,XYZCorporationThe table joins are as follows:A.EntityID = B.RowIDA.EntityID = C.RowIDI would like to select all records from Table A and display the Names fromeither Table B or Table C, depending on the Field TypeIdentifier.E.g.: SELECT Name FROM A JOIN B ON (A.EntityID = B.RowID) JOIN C ON(A.EntityID = C.RowID) IF TypeIdentifier = 0 SELECT Name FROM B IFTypeIdentifier = 1 SELECT Name FROM CResultset:SmithXYZCorporationIs this somehow possible?Thanks very much for your time & efforts!Martin

View 3 Replies View Related

JOIN Based On LIKE Condition

Apr 6, 2008

I'm trying to join two tables based on a like condition. The first table contains the full IP, e.g. '166.27.12.24' and the second contains a 2 octet range, e.g. '166.27', which I need to join.

Table 1 -> TRAFFIC (Time, SourceIP)
Table 2 -> IP_ROSTER (IP2OctetRange, Administrator)

I've tried the following, but it does not seem to work:


SELECT TOP 100
SOURCE_IP,
r.IP2OctetRange,
r.Administrator
FROM TRAFFIC
LEFT JOIN IP_ROSTER AS r
ON SOURCE_IP LIKE RTRIM(LTRIM(IP2OctetRange))+'%'

View 8 Replies View Related

Difference On Condition In JOIN && WHERE

May 3, 2007

I appreciate how difficult it is to resolve a problem without all theinformation but maybe someone has come across a similar problem.I have an 'extract' table which has 1853 rows when I ask for all rows whereperiod_ = 3. The allocation table for info has 210 rows.I have two scripts below. The first script where I specify a period on ajoin, brings back 1853 lines and works. The second script where I specifythe period in the where clause only brings back 1844 rows. I have locatedthe missing 9 rows and they don't look any different to the other 1844 rows.Can someone educate me as to the difference between specifying a conditionon a join and a condition in a where clause.SELECTa.costcentre_,b.nett_,a.*,b.*FROMextract aLEFT OUTER JOINallocation bONa.e_reg_ = b.reg_no_ANDb.period_ = 3WHEREa.period_ = 3--------------SELECTa.costcentre_,b.nett_,a.*,b.*FROMextract aLEFT OUTER JOINallocation bONa.e_reg_ = b.reg_no_WHEREa.period_ = 3ANDb.period_ = 3

View 4 Replies View Related

SQL-Question: Natural Join With Condition

Nov 10, 2007

 Hello! I have two tablestable1:                     table2:person                     Manager----------                     -----------------*id                           *id*Manager_id             *Name*Branch                   *Title Now I want to select person.* and Mangager.Name, where person.Manager_id=Manager.id.Ok, that would be easy for me, but the point is, that the column person.Manager_id might be empty or =0 (as integer), and in that case I want to have the person.* data too.So, something like Select person.*, Manager.id From person, Manager Where person.id=4 AND person.Manager=id=Manager.id doesn't work. Can somebody explain me how to fix it? I'm using MS-SQL2000 if it is important.... Best regards! 

View 1 Replies View Related

Condition In Left Outer Join

Jan 22, 2008

Hi, 
I want to get join of two tables A and B in such a way that all the Data from A for a particular Date should be extracted and then from table B Reqired column should selected against that data (it can be null). But if i use the following query:
1) Select A.*,B.Reqired from A Left Outer Join B on A.Same=B.Same Where A.Date = '1/22/2008'
it first joins the Table A and B (A contains millions of records) which takes a lot of time and then where is applied which takes a lot of time.
And if I use the following query:
2)Select A.*,B.Reqired from A Left Outer Join B on A.Same=B.Same AND A.Date = '1/22/2008'
in this case A.Date = '1/22/2008'  has no effect and all the data from the table A comes.
I have also tried first making the temp table for A.Date = '1/22/2008' and the joining with B but its cost is same as 1.
For this purpose Oracle allows the use of (+) operator like:
3)Select A.*,B.Reqired from A,B 
Where A.Same=B.Same(+) AND A.Date = '1/22/2008'  which means all data from A for '1/22/2008' Plus B.Required against it whether its null or not.
Please let me know if there is a way around in MS SQL SERVER 2005.
Thanx.
 
 

View 8 Replies View Related

Some Question About Left Join On Condition?

Oct 31, 2007

Left join on condition has no effect in the left table itself?
I have two tables:
one named student

studentID studentName
-------------------------------------------------- --------------------------------------------------
1 Jom
2 Jim
3 Tom
4 Kate
5 LinDa
6 DaiAnna


The other is grade table

studentID subject grade
-------------------------------------------------- -------------------------------------------------- -----------
1 math 90
2 Chinese 90
1 Chinese 80
3 math 98
4 math 76


And I write two SQLs
1:
select students.studentName , grades.grade as math_grade
from student as students
left outer join grade as grades on students.studentID = grades.studentID and grades.subject = 'math'
2:
select students.studentName , grades.grade as math_grade
from grade as grades
left outer join student as students on students.studentID= grades.studentID and grades.subject = 'math'

After execute, the answer is
1:
studentName math_grade
-------------------------------------------------- -----------
Jom 90
Jim NULL
Tom 98
Kate 76
LinDa NULL
DaiAnna NULL

2:
studentName math_grade
-------------------------------------------------- -----------
Jom 90
NULL 90
NULL 80
Tom 98
Kate 76


The math_grade of the second answer is all the grade rows of the grade table. on grades.subjec='math' has no effect.
Why SQL will act it like this? I know it's not a bug. But I really wanna know the inside work.
Thanks in advance, with very appreciation.

View 4 Replies View Related

UPDATE Statement With A JOIN Condition

Jun 30, 2006

Hi all,

HERE IS MY UPDATE STATEMENT:

Update WACTS_Inventory_Part_Loc_Tbl SET WIPLT_Part_New_Qty = WIPLT.WIPLT_Part_New_Qty + tmp.MATIN_QTY FROM

WACTS_Inventory_Part_Loc_Tbl WIPLT

INNER JOIN

Temp_MatIn_Data tmp

ON

WIPLT.WIPLT_Part_Number_MSBA=tmp.PARTS_ShortID

WHERE

WIPLT.WIPLT_Location_Code='MF'



I have a Problem with this satment becoz my Temp_Matin_Data_Tmp table contains two records with the same PartNumber that is (ShortId) and the two records have two different quantities.

The WACTS_Inventory_Part_Loc_Tbl has only one Part NUmber Record and the key is the PartNUmber and Location_Code.



Now when the Update Satement runs it updates the qty only from the first record of the temp table and does not update the quantity of the second record from tne source table.

I woould appreciate any help on this.



Thanks,

Jothi

View 4 Replies View Related

SQL Server 2008 :: Inner Join With Complex Condition

Mar 23, 2015

I have Two tables @master and @child

Master Table :

MasterID EntryNumber BranchId IsstockIn
1 1 1 1

2 1 1 0

Child Table:

CEntryNumber CBranchID EntryQty
1 1 10
1 1 20
1 1 -5
1 1 -4

My Query:

Select SEC.EntryQty from Item.StockEntryChild SEC
where SEC.CEntryNo =
(
select SEM.EntryNumber from item.StockEntryMaster SEM
where SEC.CBranchID=SEM.BranchID and SEC.CEntryNo=SEM.EntryNumber and SEM.MasterID=1 and SEM.isStockIn=1
)

My Result:

EntryQty
10
20
-5
-4

Expected Result:

10
20

View 6 Replies View Related

SQL Server 2008 :: Adding Condition Within Inner Join

Jul 13, 2015

I am looking for a query where in I can have a conditional statement within inner join after ON statement as shown below.

Declare @roleid int
select @roleid = roleid from Role where Name ='Admin'
select empid,empName,deptName from employee em
inner department dm on CASE when @roleid>0 then 1=dm.RoleId else em.RoleId=dm.RoleId end

View 1 Replies View Related

Diff Between LEFT OUTER JOIN And *= When Condition

Jul 23, 2005

Using SqlServer :Query 1 :SELECT def.lID as IdDefinition,TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetourFROM serveur.Data_tblDEFINITIONTABLEDECODES def,serveur.Data_tblTABLEDECODEAUNEVALEUR TDC_AUneValeurwhere def.TYPEDETABLEDECODES = 4and TDC_AUneValeur.PERIODE_ANNEEFISCALE_ID = 2and def.lID *= TDC_AUneValeur.DEFINITIONTABLEDECODES_DEFINITION_I DQuery 2 :SELECT def.lID as IdDefinition,TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetourFROM serveur.Data_tblDEFINITIONTABLEDECODES def LEFT OUTER JOINserveur.Data_tblTABLEDECODEAUNEVALEUR TDC_AUneValeurON def.lID = TDC_AUneValeur.DEFINITIONTABLEDECODES_DEFINITION_I Dwhere def.TYPEDETABLEDECODES = 4and TDC_AUneValeur.PERIODE_ANNEEFISCALE_ID = 2The query 1 returns :IdDefinition ValeurDeRetour23 null24 null25 null29 36The query 2 returns :IdDefinition ValeurDeRetour29 36The first result is the good one.How is it that the second query doesn't return the same resultSet ?I've been told about problems comparing NULL ???What is the solution ???Thanks a lot.Damien

View 3 Replies View Related

How To Write ANSI-standard JOIN ?

Oct 24, 2001

How to use ANSI-standard JOIN to write follow query which contains two outer join ?

SELECT a.*,b.title as classification,c.title as
employees,d.username,d.password,d.role_id,d.status
FROM DBO.PROFILE a,DBO.classification b,DBO.employee c ,DBO.USER d
WHERE a.user_id = 1 and a.employee_id *=c.id and a.classification_id *=b.id
and d.id= 1


Many thanks

View 1 Replies View Related

SQL Server 2012 :: Generate Flag To Check Whether Join Condition Match Or Not

Oct 12, 2015

I want to join 2 tables, table a and table b where b is a lookup table by left outer join. my question is how can i generate a flag that show whether match or not match the join condition ?

**The lookup table b for column id and country are always not null values, and both of them are the keys to join table a. This is because same id and country can have multiples rows in table a due to update date and posting date fields.

example table a
id country area
1 China Asia
2 Thailand Asia
3 Jamaica SouthAmerica
4 Japan Asia

example table b
id country area
1 China Asia
2 Thailand SouthEastAsia
3 Jamaica SouthAmerica
5 USA America

Expected output
id country area Match
1 China Asia Y
2 Thailand SouthEastAsia Y
3 Jamaica SouthAmerica Y
4 Japan Asia N

View 3 Replies View Related

Transact SQL :: How To Get First Table All Rows In Left Join If Condition Is Applied On Second Table

Aug 15, 2015

I am using stored procedure to load gridview but problem is that i am not getting all rows from first table[ Subject] on applying conditions on second table[ Faculty_Subject table] ,as you can see below if i apply condition :-

Faculty_Subject.Class_Id=@Class_Id

Then i don't get all subjects from subject table, how this can be achieved.

Sql Code:-
GO
ALTER Proc [dbo].[SP_Get_Subjects_Faculty_Details]
@Class_Id int
AS BEGIN

[code] ....

View 9 Replies View Related

Opinion

Sep 15, 2005

I have 2 tables:
Table 1 has:ID,FName,Lname
Table2 has:ID,PID,PFName,PLName, Flag
PID is a unique number -> (hh:mm:ss)
I need to run an update to table2 by setting the Flag to 1 and also creating PID.
I could use a join query to do this, but I am concern about PID (hh:mm:ss), because it may take less then 1 second to update and I will have duplicate PID.
Any idea what is the best way of doing this?

View 1 Replies View Related

I Got Into This..i Just Need Your Opinion

Nov 14, 2007

Well, its kind of tricky, and i got into this.For example i have a column of zip, all US city state name and abbreviation, preffered name and an alternate name of each city in US, and also non-acceptable name of each preffered name..

Can you give me a hint, how many databse should i used?
for example, i search for a zip code, and the result will be the name of city or an acceptable name of city and there are non-acceptable name too..

well.i just need your opinion..hope you could get into this one.tnx

View 8 Replies View Related

Looking For An Opinion

Nov 3, 2005

This is more a theoretical question so I do not have any DDL (working)to post.Let's say that I have a query which needs to be filtered for specificaccounts while also needing several joins to retrieve additional data.Is it better to so one big SELECT / JOIN / WHERE statement? As inSELECT * FROM T1JOIN T2 ON T2.[Col1] = T1.[Col1]JOIN T3 ON T3.[Col2] = T1.[Col2] AND T3.[Col3] = T1.[Col3]and so on...WHERE T1.[Account] IN ('123', '456', '789')OR is it better to do an inner SELECT / WHERE and pass that to a SELECT/ JOIN? As inSELECT * FROM(SELECT * FROM T1WHERE T1.[Account] IN ('123', '456', '789')) ITJOIN T2 ON T2.[Col1] = IT.[Col1]JOIN T3 ON T3.[Col2] = IT.[Col2] AND T3.[Col3] = IT.[Col3]and so on...First glance logic says that the inner select is the way to go sincethe joins would have less rows to work with, as opposed to joineverything and THEN pulling out what is not needed. But the queryplanner sometimes seems to have a mind of its own... Does it know thatrows will be pulled so it does that first? If I follow the same"structure" with many different queries does in us the same logic allthe time or do I need to try the same thing for each and check it?How does this apply to situations where there is a UNION involved? Do Ido the union and then apply WHERE and JOIN to filter out rows and getadditional data, respectively, or do I filter out rows inside the unionand take the combined set and do the JOINS?SELECT * FROM(SELECT T1.[Col1], T1.[Col2] FROM T1UNION ALLSELECT T2.[Col1], T2.[Col2] FROM T2) CTJOIN T2 ON T2.[Col1] = CT.[Col1]JOIN T3 ON T3.[Col2] = CT.[Col2] AND T3.[Col3] = CT.[Col3]and so on...WHERE CT.[Account] IN ('123', '456', '789')versusSELECT * FROM(SELECT T1.[Col1], T1.[Col2] FROM T1 WHERE T1.[Account] IN ('123','456', '789')UNION ALLSELECT T2.[Col1], T2.[Col2] FROM T2 WHERE T2.[Account] IN ('123','456', '789')) CTJOIN T2 ON T2.[Col1] = CT.[Col1]JOIN T3 ON T3.[Col2] = CT.[Col2] AND T3.[Col3] = CT.[Col3]and so on...

View 2 Replies View Related

Can I Print The Results Of A Condition Based On The Condition?

Feb 9, 2006

For example..

select * from mytable where MyNum = 7

If this brings back more than 1 row, I want to display a message that says,

Print 'There is more than one row returned'

Else (If only 1 row returned), I don't want to print anything.

Can I do this? Thx!

View 1 Replies View Related

COndition Spli - Error Date Condition

Apr 19, 2007

Dear friends,

I'm having a problem... maybe it's very simple, but with soo many work, right now I can't think well...



I need to filter rows in a dataflow...

I created a condition spli to that... maybe there is a better solution...

And the condition is: Datex != NULL(DT_DATE)

(Some DATE != NULL)





[Eliminar Datex NULL [17090]] Error: The expression "Datex != NULL(DT_DATE)" on "output "Case 1" (17123)" evaluated to NULL, but the "component "Eliminar Datex NULL" (17090)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error.



What is wrong??

Regards,

Pedro

View 4 Replies View Related

Reporting Services :: SSRS IIF One Condition Or IIF Another Condition

Jun 22, 2015

I  am trying to write an visibility function to have message shown based on two different IIF  conditions:

If behavior is to Add a customer ( if message =NAME ALREADY EXISTS, return " NAME ALREADY EXISTS",    otherwize return " NAME CREATED")If behavior is to  Delete a customer (( if message =NAME DOES NOT EXIST, return "NAME DOES NOT EXIST",    otherwize return "NAME SUCCESSFULLY DELETED")
I tried the following which doesn't work:
=IIF((UCase(First(Fields!Message.Value, "DataSetName")) = "NAME ALREADY EXISTS"), "WARNING: NAME ALREADY EXIST", "NAME  CREATED"),
IIF((UCase(First(Fields!Message.Value,  "DataSetName")) = " NAME DOES NOT EXIST"), "WARNING: NAME DOES NOT EXIST", " NAME DELETED")

View 6 Replies View Related

Your Opinion On Design

Mar 2, 2000

Hi,
I need your suggestions on a design issue:

I have 2 tables, Customers and Organizations. 1 Customer can be under many organizations. What would be the best way to design the db (2 choices) for performance (around 50000 customers):
1)
-Customer table
-Organization table
-Link Organization & Customer by creating a new tables with the following structure ---> CustomerID, OrgID,

2)
-A Customer tables that has a field called OrgID where the orgID is stored. If the customer has more organization related to him, we add another customer record and we put the new OrgID in the OrgID column. Examples:

Customer Table
--------------
CustomerID--Fname--Lname--Addresss-----OrgID
---------------------------------------------
1 Bob Marley 33 Africa org1
1 Bob Marley 33 Africa org2
1 Bob Marley 33 Africa org3

Please give me your suggestions,

Thank you for your time,

SASAN

View 1 Replies View Related

Need Opinion On New Purchase

Dec 18, 2007

I need to purchase a new computer for a small medical clinic which will basically only have one purpose: to answer to read and write queries to a SQL Server 2005 which is resident on that computer. Queries come from the current 8 stations (up to 14 stations in the future). Most of the time, only 3 stations will be active at a time. Queries are mostly to access patient file information, are not complex and are short-lived.

A friend of mine who owns a computer store just quoted me for a dual quad-core Xeon 5405 2GHz system with Windows Server 2003 10 Cals. I'm concerned about the following:
- What's the use in having 8 cores, each of them running at only
2GHz, when there's really only one service running (SQL Server
2005, likely Express Edition) on the computer. Does SQL Server
have the capability to make use of all cores? Otherwise, why
spend more for Xeon and so many cores instead of a single
C2D running at a faster speed of say 3GHz ?
- What would be the advantage of using a Windows Server over
Windows XP in a peer-to-peer configuration? I don't buy into
the 10 connection limit because the TCPIP.sys file can be
altered to move that limit up, so 14 stations does not trigger
the need for Windows Server in and of itself.

Thanks all!

View 5 Replies View Related

Need Opinion About This Design Thread

Apr 13, 2007

Hi all,
I am having problem with SQL connection at Godaddy where my pool connection gets MAX OUT. When it happens, I cannot access the database.This is the thread about the problem: http://forums.asp.net/thread/1665023.aspx
I just created this with "THREAD". I hope someone who has experiences with thread can give me some advice about my design. This is my first time.
 
static object Locked = new object();
public object ExecuteCommand(string queryStr, string type){     //*************************************************************************************//     // ExecuteCommand: Returns an object //    //*************************************************************************************//     Thread t = null;     lock(Locked)     {          SQLString = queryStr;                    switch(type)          {               case "ExecuteNonQuery":                   t = new Thread(ExecuteNonQuery);                   break;               case "ExecuteScalar":                    t = new Thread(ExecuteScalar);                    break;               case "GetDataReader":                    t = new Thread(GetDataReader);                    break;          }             t.Start();          t.Join();     }     return null;}
First of, does this work at all? It runs, but is it a good design in term of Thread? Since I use LOCK, do I still need the t.Join() function? The switch with the three cases, is that OKAY? Basically, I'm clueless. If you read my other post, you will get an idea what I'm trying to do. Any feedback would be very very appreciated.
Thank you.

View 2 Replies View Related

Opinion On Cube Analysis

Feb 7, 2008

Hi,

I was talking to my boss to day and our report request are not very consistant. We always having someone coming back to change something in our report. We were thinking of useing something called the Cube Analysis. Then it give our employees the raw data for them to run any standard query for themself. We have folks that want a report one way, but then they changed their minds and we are creating yet another report 4 or 5 times. what are your thoughts about this type of database?

View 6 Replies View Related

Current Opinion On UDTs?

Jun 4, 2007

What's the current opinion on UDTs? Are they valuable? Do the benefits outweigh the costs? Are they an absolute no-no? Has there been anything authorative or groundbreaking on the topic since Alex P's blog back in October 2005?



http://weblogs.asp.net/alex_papadimoulis/archive/2005/10/20/428014.aspx



View 4 Replies View Related

Need Another Opinion (Database Design)

May 8, 2007

I need some other opinions on whether or not this is considered a proper database design structure.

Here is the relationship...We have PEOPLE, that each can belong to a COMPANY.

PERSON_TABLE
Person_ID
Company_ID

COMPANY_TABLE
Company_ID

Then each person can trust other people of other companies, but can only trust 1 person per company.

My question is this. In order to maintain a constraint of 1 person per company, is it considered OK to add a the redundant column Company_ID to the PERSON_TRUSTED_TABLE(and then creating a composite primary key on the Person_ID/Company_ID) instead of just adding a trigger to the PERSON_TRUSTED_TABLE to uphold the constraint.

PERSON_TRUSTED_TABLE
Person_ID
Trusted_Company_ID
Trusted_Person_ID

I would appreciate anyone's opinion. Thanks so much!

View 5 Replies View Related

I Need A Second Opinion On SQL 2005 Syntax

Apr 25, 2006

I am not that experienced in SQL 2005 and I'm trying to debug a stored proc (written by a far more experienced programmer which is why I'm appending here.)

At the top of the proc is the statement 'DECLARE @BillActualRoom bit'. If I understand correctly this is a local variable.

Later in the code we have the statement

'SELECT @BillActualRoom = 0 FROM BillingOptions WHERE CenterID = @CenterID'

where @CenterID is an input parameter.

The table BillingOptions does have a field called 'BillActualRoom' and it is a bit.

My question is this: Does this SQL statement make any sense at all? (My gut reaction is no but I'd like a second opinion. Can't get any consensus in the office.)

I would think that putting a local variable in like this would mean that @BillActualRoom is always equal to 0.

View 5 Replies View Related

%disk Time Opinion Requested

Mar 21, 2003

Trying to deal with a user complaint of slowness. Many variables looked at which look normal (Buffer cache, queue length,memory). Probably looks like a network issue. My question is what people consider acceptable when it comes to %disk time. My %disk time has increased from an average of 20% to 33% in recent months. My average disk read and average disk write have both been less than one. MY research has showed that more than 55% %disk time for ten minutes is considered a problem. Not there yet but seem to be slowly getting there. THe app running against my server is vendor written so can't change, also running log shipping which is probably inflating the numbers a little.
Any opinions appreciated.

View 1 Replies View Related

% Processor And % User Time Opinion

May 26, 2004

I have a server with little control over most of the codeset and db design. Recently I have seen both the Processor - %Processor time and Processor - % User time go fom about 6.3 to about 24.3. The system queue length has also gone from about .2 to 1.1. In my humble opinion both of the are signs of a problem coming (luckily the cache hit ratio is still sitting at about 99%). I have been running profiler to catch the things that take more that 4500 MS, and I can probably tie the 2 together. Any opinions, or real world comparisons appreciated

View 3 Replies View Related

Opinion Requested On Developer Permissions

Feb 27, 2004

We are trying to restrict developer permissions in our development environment. One thought is to add developers to db_datareader, db_datawriter, db_ddladmin, db_securityadmin and then revoke various permissions from ddladmin and securityadmin. The goal is to allow developer to create stored procedures and assign permissions to the stored procedures.

Another option is to place all developers in the same role and ask them to create all procedures using that role name (ex: dev_role.sp_procedurename). By doing this each developer will be able to run stored procedures created by another developer. The down side is the permissions do not match Model Office/User Test and Production.

Any suggestions on how to handle this situation?

Thanks, Dave

View 2 Replies View Related

Want Expert Opinion On A Way Of Storing 'relations'

Jul 20, 2005

i've a database where relations are hold in a special way which the projectleaders think of as "performant and uncomplicated" but which is veryquestionable to me:------------------------------------------------Table [Attributes]Fields [AttributeID] and [AttributeText]Table [Objects]Fields object stuff.... and [AttributeIDs] (varchar with 0-20 ids usually)in AttributeIDs there is a backslash separted list of Attribute-IDs like'3412278'so to get 20 object with a special attribute (which we need often) we doSELECT TOP 20 *FROM ObjectsINNER JOIN AttributesON (Objects.AttributeIDs LIKE ('%' + (CAST AttributeID AS varchar) + '\%'))ORDER BY ObjectTextps: to store data we need for communication we include a dozen of fields in*every* table and its content makes about 100 bytes/record------------------------------------------------i would do this stuff with a table to store the object/attributecorrelations.could someone tell me if that stuff makes any sense to an expert and how tovaluate it in regard of performance(we have big customers where that *is* anissue), design, scalability, pragmatism and sense ;)thanks in advance,ViperDK

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved