HI
Its been a while since i have posted here, but this one has stumped me. maybe i am just super hungry right now and missing something obvious... but i can get this one.
i would search the forums for the answer, but not even sure what to search for... not even sure if this (left joins) is the way to accomplish what i want.
Using MS-SQL database as the back end.
I have 3 data tables.
Table 1 is a cross reference table.
Table 2 is the primary data (group data)
Table 3 is the secondary data (users data) (multiple users in a group)
table 1 provides the text that matches the respective status from both the 2nd and 3rd tables.
xRefTable
MemberStatus MemberStatusText
---- ------
0 Non Member
1 Referral
2 Resident
3 Non Resident
PrimaryUserTable
GroupID GroupStatus
-- ----
1 0
2 3
3 1
4 2
SecondaryDataTable
MemberID GroupID MemberStatus
--- --- ----
1 1 0
2 1 0
3 1 0
4 1 0
5 2 3
6 2 3
7 3 1
8 3 1
9 4 2
10 4 1
11 4 1
12 4 3
The ultimate results i am trying to get is something like :
The Results should be able to yield
GroupID GroupStatus GroupStatusText MemberID MemberStatus MemberStatusText
--- ---- ----- --- ---- ------
4 2 Resident 9 2 Resident
4 2 Resident 10 1 Referral
4 2 Resident 11 1 Referral
4 2 Resident 12 3 Non Resident
i am trying to do this with a single SQL query, i am sure it is possible, but cant finger out how to structure it. this is what i was thinking, but i am sure you can see its not going to work.
Lines 4 and 7 are the hangup i believe.
Since both are referring to 'MemberStatusText', how do i specify that line 4 relates to the primary data and line 7 related to the secondary data ?
1 SELECT
2 PrimaryUserTable.GroupID,
3 PrimaryUserTable.GroupStatus,
4 xRefTable.MemberStatusText as GroupStatusText
5 SecondaryDataTable.MemberID,
6 SecondaryDataTable.MemberStatus,
7 xRefTable.MemberStatusText as MemberStatusText
8 LEFT JOIN SecondaryDataTable on (PrimaryUserTable.GroupID = SecondaryDataTable.GroupID)
9 LEFT JOIN xRefTable on (PrimaryUserTable.GroupStatus = xRefTable.MemberStatus)
10 LEFT JOIN xRefTable on (SecondaryDataTable.MemberStatus = xRefTable.MemberStatus)
11 WHERE GroupID = 4
12 ORDER BY GroupID, MemberIDSince both are referring to 'MemberStatusText', how do i specify that line 4 relates to the primary data and line 7 related to the secondary data ?using table aliasesSELECT PrimaryUserTable.GroupID
, PrimaryUserTable.GroupStatus
, GroupXref.MemberStatusText as GroupStatusText
, SecondaryDataTable.MemberID
, SecondaryDataTable.MemberStatus
, MemberXref.MemberStatusText as MemberStatusText
FROM PrimaryUserTable
INNER
JOIN SecondaryDataTable
on SecondaryDataTable.GroupID = PrimaryUserTable.GroupID
INNER
JOIN xRefTable as GroupXref
on GroupXref.MemberStatus = PrimaryUserTable.GroupStatus
INNER
JOIN xRefTable as MemberXref
on MemberXref.MemberStatus = SecondaryDataTable.MemberStatus
WHERE PrimaryUserTable.GroupID = 4
ORDER
BY PrimaryUserTable.GroupID
, SecondaryDataTable.MemberID:)|||EXCELLENT !
exactly what i needed. the table alisases were just skipping from my mind that night.
THANK YOUsql
Showing posts with label query. Show all posts
Showing posts with label query. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
duplicate rows
Hi,
I have the following records in my sql table and want to place all the duplicates in one row. I tried to do this with an update query but had no success. I can do this in access but can't figure out how to do it in sql. Any help would be great. Thanks.
mr4100 wrote:
... want to place all the duplicates in one row.
Can you explain a litlle more about what you mean?
|||if you take a look at the picture, you can see there are 3 codes of 379. there are other columns attached to this table i just didn't show all of them. I want to loop through the table and place the 3 rows with the same code in a row by themselves. I'm sending an email from the table and don't want to send 3 seperate emails, just one email containing data about all 3 rows. My task sends an email for each row in the table. I hope I didn't confuse you.
thanks,
|||It is still not clear what your desired output may look like. I suggest that if would be helpful if you posted the table DDL, some sample data in the form of INSERT statements, and what the desired output looks like. Also the version of SQL Server would be helpful in finding a solution.
I'm assuming you have explored the various ways to use GROUP BY...
|||The confusing part is when you say: duplicates. There are not duplicates here, but rather it is just a typical situation with (hopefully) a parent table and key (for major) and then the child rows with different names. The best resource for how to do this is here:
http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html
The site is sadly an eyesore these days, but the information is still good. Use the 2005 version for sure if you are using 2005, it is really excellent and works nice.
|||what i want to do is take these 3 seperate rows that are in the table below with the same major and put them into 1 row by themselves whether it would be updating this table or a new table by themselves,
start with this:
name major desc
john 45 eng.
mary 45 eng.
corey 25 math
rose 45 eng.
sue 15 mus.
end with this:
name major desc
john,mary,rose 45 eng.
corey 25 math
sue 15 mus.
|||Here it is.. if you use sql server 2005
Code Snippet
Create Table #data (
[name] Varchar(100) ,
[major] Varchar(100) ,
[desc] Varchar(100)
);
Insert Into #data Values('john','45','eng.');
Insert Into #data Values('mary','45','eng.');
Insert Into #data Values('corey','25','math');
Insert Into #data Values('rose','45','eng.');
Insert Into #data Values('sue','15','mus.');
Select Distinct
Substring((Select ',' + name [text()] from #data sub where sub.major=main.major and sub.[desc]=main.[desc] For Xml Path('')),2,8000)
,major
,[desc]
from
#data main
|||this works perfectly! Could you explain why you use the #data sub and #data main and for xml Path(")),2,8000)?
I would just like to know what it is does to make this work, I would have never figured this out.
Thanks,
duplicate results returned in query
I am getting duplicate results returned in this query if someone comments on a news item :( if i could limit results to 1 for just post_id it would be perfect but not sure how to integrete it into this query.
This is a query part of a php script I wrote to display the news posted in a thread... You can view the final result at http://overclocked.lancamp.com to see what im talking about.
SELECT t.topic_id, x.bbcode_uid, t.topic_title, x.post_text, t.topic_time, t.topic_views, t.topic_replies, t.topic_poster, u.username, p.enable_html, p.enable_bbcode, p.enable_smilies
FROM phpbb_topics t, phpbb_posts p, phpbb_posts_text x, phpbb_users u
WHERE t.forum_id =16
AND p.forum_id =16 AND p.topic_id = t.topic_id AND x.post_id = t.topic_first_post_id AND u.user_id = t.topic_poster
ORDER BY p.post_time DESC
Thanks for whoever helps!Hi,
This is not so easy to answer while I don't know your tables.
Normally you can avoid double results with DISTINCT in the SELECT clause.
advice: build a simple simpel test database and with a few tables and a few field in each table. Than try the same query.
If the fault still remains please send me the tables and the exact query.
Best regards
Robertsql
This is a query part of a php script I wrote to display the news posted in a thread... You can view the final result at http://overclocked.lancamp.com to see what im talking about.
SELECT t.topic_id, x.bbcode_uid, t.topic_title, x.post_text, t.topic_time, t.topic_views, t.topic_replies, t.topic_poster, u.username, p.enable_html, p.enable_bbcode, p.enable_smilies
FROM phpbb_topics t, phpbb_posts p, phpbb_posts_text x, phpbb_users u
WHERE t.forum_id =16
AND p.forum_id =16 AND p.topic_id = t.topic_id AND x.post_id = t.topic_first_post_id AND u.user_id = t.topic_poster
ORDER BY p.post_time DESC
Thanks for whoever helps!Hi,
This is not so easy to answer while I don't know your tables.
Normally you can avoid double results with DISTINCT in the SELECT clause.
advice: build a simple simpel test database and with a few tables and a few field in each table. Than try the same query.
If the fault still remains please send me the tables and the exact query.
Best regards
Robertsql
Duplicate results in 2 columns but reversed
I have to write a query which extracts everyone from a table who has the same surname and forenames as someone else but different id's.
The query should have a surname column, a forenames column, and two id columns (from the person column of the table).
I need to avoid duplicates i.e. the first table id should only be returned in the first id column and not in the second - which is what i am getting at the mo.
This is what i have done
select first.surname, first.forenames, first.person, second.person
from shared.people first, shared.people second
where first.surname= second.surname
and first.forenames = second.forenames
and not first.person = second.person
order by first.surname, first.forenames
and i get results like this
Porter Sarah Victoria 9518823 9869770
Porter Sarah Victoria 9869770 9518823 - i.e. duplicates
cheerswhat about:
select
first.surname,
first.forenames,
min(first.person),
min(second.person)
from
shared.people first, shared.people second
where
first.surname= second.surname
and first.forenames = second.forenames
and not first.person = second.person
group by
first.surname,
first.forenames
order by
first.surname,
first.forenames
I'm not positive that this would work if there were more than one duplicate entry.
regards,
hmscott|||Thanks for the quick reply,
Yes, it seems to work if the first column is called min(first.person) and the second is called max(second.person) but this would fail if there were more than one duplicate.
Any ideas?
Cheers :)|||This will be a pig, so I hope you only need to run this once...
select
first.surname,
first.forenames,
min(first.person),
min(second.person)
from
shared.people first, shared.people second
where
first.surname= second.surname
and first.forenames = second.forenames
and first.person < second.person
group by
first.surname,
first.forenames
order by
first.surname,
first.forenames|||I hope you are talking about something like this:
drop table #tmp
create table #tmp(id int,fname varchar(10),lname varchar(10))
insert #tmp values(1,'a','b')
insert #tmp values(2,'b','b')
insert #tmp values(3,'a','b')
insert #tmp values(4,'f','b')
insert #tmp values(5,'b','b')
insert #tmp values(6,'b','b')
insert #tmp values(7,'a','b')
select distinct t.fname,t.lname,t.id,t2.id
from #tmp t
join #tmp t2 on t2.fname=t.fname and t2.lname=t.lname and t2.id<>t.id
where t.fname+t.lname in(
select fname+lname
from #tmp
group by fname+lname
having count(*)>1)
order by 1,2,3,4
--- OR
select fname,lname,id
from #tmp
where fname+lname in(
select fname+lname
from #tmp
group by fname+lname
having count(*)>1 )
order by 1,2,3|||cheers all - thanks for the quick responses!
:D
The query should have a surname column, a forenames column, and two id columns (from the person column of the table).
I need to avoid duplicates i.e. the first table id should only be returned in the first id column and not in the second - which is what i am getting at the mo.
This is what i have done
select first.surname, first.forenames, first.person, second.person
from shared.people first, shared.people second
where first.surname= second.surname
and first.forenames = second.forenames
and not first.person = second.person
order by first.surname, first.forenames
and i get results like this
Porter Sarah Victoria 9518823 9869770
Porter Sarah Victoria 9869770 9518823 - i.e. duplicates
cheerswhat about:
select
first.surname,
first.forenames,
min(first.person),
min(second.person)
from
shared.people first, shared.people second
where
first.surname= second.surname
and first.forenames = second.forenames
and not first.person = second.person
group by
first.surname,
first.forenames
order by
first.surname,
first.forenames
I'm not positive that this would work if there were more than one duplicate entry.
regards,
hmscott|||Thanks for the quick reply,
Yes, it seems to work if the first column is called min(first.person) and the second is called max(second.person) but this would fail if there were more than one duplicate.
Any ideas?
Cheers :)|||This will be a pig, so I hope you only need to run this once...
select
first.surname,
first.forenames,
min(first.person),
min(second.person)
from
shared.people first, shared.people second
where
first.surname= second.surname
and first.forenames = second.forenames
and first.person < second.person
group by
first.surname,
first.forenames
order by
first.surname,
first.forenames|||I hope you are talking about something like this:
drop table #tmp
create table #tmp(id int,fname varchar(10),lname varchar(10))
insert #tmp values(1,'a','b')
insert #tmp values(2,'b','b')
insert #tmp values(3,'a','b')
insert #tmp values(4,'f','b')
insert #tmp values(5,'b','b')
insert #tmp values(6,'b','b')
insert #tmp values(7,'a','b')
select distinct t.fname,t.lname,t.id,t2.id
from #tmp t
join #tmp t2 on t2.fname=t.fname and t2.lname=t.lname and t2.id<>t.id
where t.fname+t.lname in(
select fname+lname
from #tmp
group by fname+lname
having count(*)>1)
order by 1,2,3,4
--- OR
select fname,lname,id
from #tmp
where fname+lname in(
select fname+lname
from #tmp
group by fname+lname
having count(*)>1 )
order by 1,2,3|||cheers all - thanks for the quick responses!
:D
Duplicate Records in a table
How do i remove duplicate records from a table with a single query without using cursors or anything like that.
Sample :
tempCol
1
1
2
2
1
P.S The table has only one column
Usually way is: in your query, create a temp table to hold the distinct records from your physical table, then, delete the rows in the physical table, then, insert back from your temp table, finally, drop the temp table.
|||che3358gives a good way to do it. The only thing I'd add is that you can make it all go faster by doing it only for the duplicate rows, ie, instead of doing it for distinct rows, do a count(*) and a having, ie,
select field1, field2, field3
into #DupTable
from mytable
group by field1, field2, field3
having count(*) > 1
delete mytable
from mytable a, #DupTable b
where a.field1 = a.field1
and b.field2 = b.field2
and ....
insert into mytable (field1, field2, ...)
select field1, field2, ...
from #DupTable
Duplicate records - SQL Below
Everytime, I run this query I get 3 or 4 duplicate records. I can't figure out what is going on. Any help would be appreciated. Thanks
Code: ( sql )
- SELECT dbo.INVOICES.ORDER_NO AS "ORDER_NO", dbo.INVOICES.SALES_REP AS "SALES_REP", dbo.INVOICES.TERMS AS "TERMS", convert (varchar,cast (dbo.INVOICES.INV_AMOUNT AS money),1) AS "INV_AMOUNT", dbo.INVOICES.STATUS AS "STATUS", dbo.TRCK_GRP.NAME AS "GROUP NAME", dbo.TRCK_CHO.RANK AS "CHOICE RANK", dbo.TRCK_CHO.NAME AS "CHOICE NAME", dbo.TRCK_CHO.IS_DEFAULT AS "IS_DEFAULT", dbo.TRCK_GRP.RANK AS "GROUP RANK", convert(varchar,dbo.TRCK_SEL.MODIFIED_DATE,101) AS "MODIFIED_DATE", dbo.TRCK_SEL.SUB_CODE AS "SUB_CODE", dbo.TRCK_SEL.SUB_TYPE AS "SUB_TYPE", dbo.INVOICES.ORDER_DATE AS "ORDER_DATE", dbo.TRCK_SEL.GROUP_CODE AS "GROUP_CODE", dbo.TRCK_CHO.CHOI_CODE AS "CHOI_CODE", dbo.CUST.NAME AS "NAME", dbo.PERSONAL.PFIRST AS "PFIRST", dbo.PERSONAL.EMAIL AS "EMAIL" FROM ((((((dbo.TRCK_GRP INNER JOIN dbo.TRCK_CHO ON dbo.TRCK_GRP.GROUP_CODE = dbo.TRCK_CHO.GROUP_CODE) INNER JOIN dbo.TRCK_SEL ON dbo.TRCK_CHO.CHOI_CODE = dbo.TRCK_SEL.CHOI_CODE) INNER JOIN dbo.INVOICES ON dbo.TRCK_SEL.SUB_CODE = dbo.INVOICES.ORDER_NO) INNER JOIN dbo.CUST ON dbo.INVOICES.CUST_CODE = dbo.CUST.CUST_CODE) INNER JOIN dbo.ADDRESS ON dbo.CUST.CUST_CODE = dbo.ADDRESS.CUST_CODE) LEFT OUTER JOIN dbo.PERSONAL ON dbo.PERSONAL.IDNO = dbo.INVOICES.SALES_REP) WHERE dbo.INVOICES.STATUS = 8 AND dbo.TRCK_GRP.NAME LIKE 'CREDIT CARD AUTHORIZATION' AND dbo.TRCK_CHO.NAME IN ( 'AWAITING SIGNED CC AUTHORIZATION FORM' , 'CREDIT CARD DECLINED / EXPIRED' ) AND dbo.INVOICES.TERMS = 'CC' AND dbo.INVOICES.PAID = 'F' AND convert(varchar,dbo.TRCK_SEL.MODIFIED_DATE,101) = '{%Current Date MM/DD/YYYY%}' ORDER BY dbo.INVOICES.ORDER_NO ASC
Kindly post your table structure.
Duplicate records
Hi
Can anyone tell me how to stop a SQL query displaying duplicate records within a table
Thanks Alotuse the distinct keyword along with select|||will try - thanks alotsql
Can anyone tell me how to stop a SQL query displaying duplicate records within a table
Thanks Alotuse the distinct keyword along with select|||will try - thanks alotsql
Thursday, March 22, 2012
Duplicate Entries
I have an issue where certain parts of data are repeated several times after i create my query. Without providing my SQL code for now could anyone suggest possibly the main reason(s) for data being duplicated?
Thanks
I reckon its because either.....
a) you have duplicate data in your tables
b) you are doing a JOIN to a table with a one -> many relationship
And as a solution i reckon you could....
a) use DISTINCT in your select statement
Am I close?
|||I think your very close:
a) Definately not as i recreated another query without all the fields that i originally needed and had NO duplicate items.
b) This maybe it but ill have to investigate it and get back to you.
Based on your answers and solution
1. How can i easily define which tables may have 1-Many relationship?
2. Where would i use the Distinct statement?
Thanks
|||1. Don't think you can programmatically find out 1->Many relationships. Hopefully you have your relationships defined by foreign keys which should point you in the right direction. Maybe use sp_fkeys to help identify them eg EXEC sp_fkey 'YourTable', 'dbo'
2. SELECT DISTINCT Col1, Col2, Col3.....
Check Books Online for more details.
sqlDuplicate display of values in the Details section
Hi folks,
i have a peculiar problem, i am passing a query from VB 6.0 to crystal reports to revieve the data i want and i am successful in doing that. But the problem is, it is
duplicating the output, i'm seeking for. it duplicates when the output is places in the Details section but when i place it in the Header section then there is no duplication.
can any one please tell me why?
thanx in advance.......
number3I hope I've understood the question correctly...
How many rows is your query returning? The details section is the only section that creates a new section for every row returned by the query. If you drag the field into the header you are only going to get the value from the first row. This is how crystal is supposed to work.
If you need a second details section (from another result set), you need to look at placing a subreport in the header.|||you need to run your SQL in a database viewer tool. possibly you have linked some tables together but not put a criteria in the select expert..
you CAN choose "select distinct records" on the database menu, but this will make the report run more slowly. you should really fix up your sql so it doesnt return you duplicatessql
i have a peculiar problem, i am passing a query from VB 6.0 to crystal reports to revieve the data i want and i am successful in doing that. But the problem is, it is
duplicating the output, i'm seeking for. it duplicates when the output is places in the Details section but when i place it in the Header section then there is no duplication.
can any one please tell me why?
thanx in advance.......
number3I hope I've understood the question correctly...
How many rows is your query returning? The details section is the only section that creates a new section for every row returned by the query. If you drag the field into the header you are only going to get the value from the first row. This is how crystal is supposed to work.
If you need a second details section (from another result set), you need to look at placing a subreport in the header.|||you need to run your SQL in a database viewer tool. possibly you have linked some tables together but not put a criteria in the select expert..
you CAN choose "select distinct records" on the database menu, but this will make the report run more slowly. you should really fix up your sql so it doesnt return you duplicatessql
Wednesday, March 21, 2012
Dumping all SQL queries.
Hi - is it possible to dump the query text of every query run
against a particular database, or all databases, in an SQL Server
2000 installation? We've an issue whereby a 3rd. party application
running queries over ODBC sometimes claims that gobbledegook is
returned, and it would be very useful to identify the query that's
being claimed is the problem. I suspect that it's an application
issue rather than a SQL Server one.
against a particular database, or all databases, in an SQL Server
2000 installation? We've an issue whereby a 3rd. party application
running queries over ODBC sometimes claims that gobbledegook is
returned, and it would be very useful to identify the query that's
being claimed is the problem. I suspect that it's an application
issue rather than a SQL Server one.
__________________________________________________ ___________
Are you Catholic ?
http://www.CatholicEmail.com
100s of FREE email addresses -->
http://www.UltimateEmail.com
Send an Online Greeting Card http://www.UltimateEcards.com"sqlserver yeahbaby" <Use-Author-Address-Header@.[127.1]> wrote in message
news:20031104161149.53F073965@.sitemail.everyone.ne t...
> Hi - is it possible to dump the query text of every query run
> against a particular database, or all databases, in an SQL Server
> 2000 installation? We've an issue whereby a 3rd. party application
> running queries over ODBC sometimes claims that gobbledegook is
> returned, and it would be very useful to identify the query that's
> being claimed is the problem. I suspect that it's an application
> issue rather than a SQL Server one.
Look at profiler.
> __________________________________________________ ___________
> Are you Catholic ?
> http://www.CatholicEmail.com
> 100s of FREE email addresses -->
> http://www.UltimateEmail.com
> Send an Online Greeting Card http://www.UltimateEcards.com
Monday, March 19, 2012
dumbest question of the day: run a simple query and save the results into a text file
Can someone demonstrate a SIMPLE way to do this that does not require
additional functions or stored procedures to be created? Lets say I
want to execute the following simple query - "select * from clients" -
and save the results to a text file, we will assume c:\results.txt
additional functions or stored procedures to be created? Lets say I
want to execute the following simple query - "select * from clients" -
and save the results to a text file, we will assume c:\results.txt
How can I do this in one step?Here is one way, from the command line:
isql -S<yourserver>-U<username>-P<password> -Q "select * from clients" -o
output.log
hth
"sumGirl" <emebohw@.netscape.net> wrote in message
news:a5e13cff.0411301337.69b2ff44@.posting.google.c om...
> Can someone demonstrate a SIMPLE way to do this that does not require
> additional functions or stored procedures to be created? Lets say I
> want to execute the following simple query - "select * from clients" -
> and save the results to a text file, we will assume c:\results.txt
> How can I do this in one step?|||The BCP command line works as well, and you get a little more control over
how the data is saved.
Also there are a boat load of XML functions, if you need data saved in that
format.
"JD" <joeydba@.yahoo.com> wrote in message news:41acf2d6$1@.news.qgraph.com...
> Here is one way, from the command line:
> isql -S<yourserver>-U<username>-P<password> -Q "select * from clients" -o
> output.log
> hth
>
> "sumGirl" <emebohw@.netscape.net> wrote in message
> news:a5e13cff.0411301337.69b2ff44@.posting.google.c om...
>> Can someone demonstrate a SIMPLE way to do this that does not require
>> additional functions or stored procedures to be created? Lets say I
>> want to execute the following simple query - "select * from clients" -
>> and save the results to a text file, we will assume c:\results.txt
>>
>> How can I do this in one step?|||I don't know if you want it to be ad-hoc, but if you do, you can run
the query from Query Analyzer and from the query menu pick "results to
file"
Sunday, March 11, 2012
Dumb Date Format Question
I want the following query to result in "2004/10".
SELECT DATEPART(yyyy, datein) & '/' & DATEPART(mm, datein) AS theDate
From myTable
However, I get the error:
Syntax error converting the varchar value '/' to a column of data type int.
Is there a better way to do this?
Also, I realize I may be asking this in the wrong forum. Is there a place
for MSSQL newbies to ask their dumb questions?
Thank you!
MatthewHi Matthew
Ask whatever you like here, just don't call anything a dumb question. That
way, you give people who have only been using the product a bit longer than
you a chance to answer questions and feel like they have learned something.
But nobody wants to feel like they can only answer DUMB questions. ;-)
& is the bitwise 'AND' operator, and its operands must be integers. Please
read about bit operations in the Books Online.
My guess is that you want to concatenate strings. In TSQL we use '+' for
concatenation.
But, it still won't work because datepart returns a numeric value. Try using
datename. Or converting to character before you concatenate.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Matthew" <turn.deletethis@.alltel.net> wrote in message
news:O6wl$vfvEHA.3276@.TK2MSFTNGP15.phx.gbl...
>I want the following query to result in "2004/10".
> SELECT DATEPART(yyyy, datein) & '/' & DATEPART(mm, datein) AS theDate
> From myTable
> However, I get the error:
> Syntax error converting the varchar value '/' to a column of data type
> int.
> Is there a better way to do this?
> Also, I realize I may be asking this in the wrong forum. Is there a place
> for MSSQL newbies to ask their dumb questions?
> Thank you!
> Matthew
>|||> Ask whatever you like here, just don't call anything a dumb question. That
> way, you give people who have only been using the product a bit longer
> than you a chance to answer questions and feel like they have learned
> something. But nobody wants to feel like they can only answer DUMB
> questions. ;-)
Sorry about that. I'll be more careful in the future.
> My guess is that you want to concatenate strings. In TSQL we use '+' for
> concatenation.
Yup, that's right.
> But, it still won't work because datepart returns a numeric value. Try
> using datename. Or converting to character before you concatenate.
OK, I'll give that a try.
Thank you!
Matthew
SELECT DATEPART(yyyy, datein) & '/' & DATEPART(mm, datein) AS theDate
From myTable
However, I get the error:
Syntax error converting the varchar value '/' to a column of data type int.
Is there a better way to do this?
Also, I realize I may be asking this in the wrong forum. Is there a place
for MSSQL newbies to ask their dumb questions?
Thank you!
MatthewHi Matthew
Ask whatever you like here, just don't call anything a dumb question. That
way, you give people who have only been using the product a bit longer than
you a chance to answer questions and feel like they have learned something.
But nobody wants to feel like they can only answer DUMB questions. ;-)
& is the bitwise 'AND' operator, and its operands must be integers. Please
read about bit operations in the Books Online.
My guess is that you want to concatenate strings. In TSQL we use '+' for
concatenation.
But, it still won't work because datepart returns a numeric value. Try using
datename. Or converting to character before you concatenate.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Matthew" <turn.deletethis@.alltel.net> wrote in message
news:O6wl$vfvEHA.3276@.TK2MSFTNGP15.phx.gbl...
>I want the following query to result in "2004/10".
> SELECT DATEPART(yyyy, datein) & '/' & DATEPART(mm, datein) AS theDate
> From myTable
> However, I get the error:
> Syntax error converting the varchar value '/' to a column of data type
> int.
> Is there a better way to do this?
> Also, I realize I may be asking this in the wrong forum. Is there a place
> for MSSQL newbies to ask their dumb questions?
> Thank you!
> Matthew
>|||> Ask whatever you like here, just don't call anything a dumb question. That
> way, you give people who have only been using the product a bit longer
> than you a chance to answer questions and feel like they have learned
> something. But nobody wants to feel like they can only answer DUMB
> questions. ;-)
Sorry about that. I'll be more careful in the future.
> My guess is that you want to concatenate strings. In TSQL we use '+' for
> concatenation.
Yup, that's right.
> But, it still won't work because datepart returns a numeric value. Try
> using datename. Or converting to character before you concatenate.
OK, I'll give that a try.
Thank you!
Matthew
Dumb Date Format Question
I want the following query to result in "2004/10".
SELECT DATEPART(yyyy, datein) & '/' & DATEPART(mm, datein) AS theDate
From myTable
However, I get the error:
Syntax error converting the varchar value '/' to a column of data type int.
Is there a better way to do this?
Also, I realize I may be asking this in the wrong forum. Is there a place
for MSSQL newbies to ask their dumb questions?
Thank you!
Matthew
Hi Matthew
Ask whatever you like here, just don't call anything a dumb question. That
way, you give people who have only been using the product a bit longer than
you a chance to answer questions and feel like they have learned something.
But nobody wants to feel like they can only answer DUMB questions. ;-)
& is the bitwise 'AND' operator, and its operands must be integers. Please
read about bit operations in the Books Online.
My guess is that you want to concatenate strings. In TSQL we use '+' for
concatenation.
But, it still won't work because datepart returns a numeric value. Try using
datename. Or converting to character before you concatenate.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Matthew" <turn.deletethis@.alltel.net> wrote in message
news:O6wl$vfvEHA.3276@.TK2MSFTNGP15.phx.gbl...
>I want the following query to result in "2004/10".
> SELECT DATEPART(yyyy, datein) & '/' & DATEPART(mm, datein) AS theDate
> From myTable
> However, I get the error:
> Syntax error converting the varchar value '/' to a column of data type
> int.
> Is there a better way to do this?
> Also, I realize I may be asking this in the wrong forum. Is there a place
> for MSSQL newbies to ask their dumb questions?
> Thank you!
> Matthew
>
|||> Ask whatever you like here, just don't call anything a dumb question. That
> way, you give people who have only been using the product a bit longer
> than you a chance to answer questions and feel like they have learned
> something. But nobody wants to feel like they can only answer DUMB
> questions. ;-)
Sorry about that. I'll be more careful in the future.
> My guess is that you want to concatenate strings. In TSQL we use '+' for
> concatenation.
Yup, that's right.
> But, it still won't work because datepart returns a numeric value. Try
> using datename. Or converting to character before you concatenate.
OK, I'll give that a try.
Thank you!
Matthew
SELECT DATEPART(yyyy, datein) & '/' & DATEPART(mm, datein) AS theDate
From myTable
However, I get the error:
Syntax error converting the varchar value '/' to a column of data type int.
Is there a better way to do this?
Also, I realize I may be asking this in the wrong forum. Is there a place
for MSSQL newbies to ask their dumb questions?
Thank you!
Matthew
Hi Matthew
Ask whatever you like here, just don't call anything a dumb question. That
way, you give people who have only been using the product a bit longer than
you a chance to answer questions and feel like they have learned something.
But nobody wants to feel like they can only answer DUMB questions. ;-)
& is the bitwise 'AND' operator, and its operands must be integers. Please
read about bit operations in the Books Online.
My guess is that you want to concatenate strings. In TSQL we use '+' for
concatenation.
But, it still won't work because datepart returns a numeric value. Try using
datename. Or converting to character before you concatenate.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Matthew" <turn.deletethis@.alltel.net> wrote in message
news:O6wl$vfvEHA.3276@.TK2MSFTNGP15.phx.gbl...
>I want the following query to result in "2004/10".
> SELECT DATEPART(yyyy, datein) & '/' & DATEPART(mm, datein) AS theDate
> From myTable
> However, I get the error:
> Syntax error converting the varchar value '/' to a column of data type
> int.
> Is there a better way to do this?
> Also, I realize I may be asking this in the wrong forum. Is there a place
> for MSSQL newbies to ask their dumb questions?
> Thank you!
> Matthew
>
|||> Ask whatever you like here, just don't call anything a dumb question. That
> way, you give people who have only been using the product a bit longer
> than you a chance to answer questions and feel like they have learned
> something. But nobody wants to feel like they can only answer DUMB
> questions. ;-)
Sorry about that. I'll be more careful in the future.
> My guess is that you want to concatenate strings. In TSQL we use '+' for
> concatenation.
Yup, that's right.
> But, it still won't work because datepart returns a numeric value. Try
> using datename. Or converting to character before you concatenate.
OK, I'll give that a try.
Thank you!
Matthew
Duduce difference
Hi all,
I have a query that adds up two tables as below:
SELECT SUM(RPAAP / 100) AS [Sales Ledger]
FROM F03B11
SELECT SUM(GBAPYC + GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 +
GBAN06 + GBAN07 + GBAN08 + GBAN09 + GBAN10 + GBAN11 + GBAN12)/100
AS GL
FROM F0902
WHERE (GBAID = '00667809') AND (GBFY = 3)
I would like to take this a step further and deduce the difference
between the two. However I?m not sure how this should be done.
Any help would be most welcome.
Sam
?new person on windows 2000?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Use sub queries thus:
select [Sales Ledger] - gl as difference
from
(SELECT SUM(RPAAP / 100) AS [Sales Ledger] FROM F03B11) Q1 join
(SELECT SUM(GBAPYC + GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 + GBAN06
+ GBAN07 + GBAN08 + GBAN09 + GBAN10 + GBAN11 + GBAN12)/100
AS GL
FROM F0902
WHERE (GBAID = '00667809') AND (GBFY = 3)) Q2
Assuming Q2 returns just the one row
"Sam G" <moby@.spamhole.com> wrote in message
news:%23aCWsvTnDHA.2628@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I have a query that adds up two tables as below:
> SELECT SUM(RPAAP / 100) AS [Sales Ledger]
> FROM F03B11
> SELECT SUM(GBAPYC + GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 +
> GBAN06 + GBAN07 + GBAN08 + GBAN09 + GBAN10 + GBAN11 + GBAN12)/100
> AS GL
> FROM F0902
> WHERE (GBAID = '00667809') AND (GBFY = 3)
> I would like to take this a step further and deduce the difference
> between the two. However I'm not sure how this should be done.
> Any help would be most welcome.
> Sam
> "new person on windows 2000"
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003|||SELECT SUM(RPAAP / 100) AS [Sales Ledger] -
(SELECT SUM(GBAPYC + GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 +
GBAN06 + GBAN07 + GBAN08 + GBAN09 + GBAN10 + GBAN11 + GBAN12)/100
AS GL
FROM F0902
WHERE (GBAID = '00667809') AND (GBFY = 3))
FROM F03B11
Hope this helps
Wayne Snyder, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sam G" <moby@.spamhole.com> wrote in message
news:%23aCWsvTnDHA.2628@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I have a query that adds up two tables as below:
> SELECT SUM(RPAAP / 100) AS [Sales Ledger]
> FROM F03B11
> SELECT SUM(GBAPYC + GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 +
> GBAN06 + GBAN07 + GBAN08 + GBAN09 + GBAN10 + GBAN11 + GBAN12)/100
> AS GL
> FROM F0902
> WHERE (GBAID = '00667809') AND (GBFY = 3)
> I would like to take this a step further and deduce the difference
> between the two. However I'm not sure how this should be done.
> Any help would be most welcome.
> Sam
> "new person on windows 2000"
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
I have a query that adds up two tables as below:
SELECT SUM(RPAAP / 100) AS [Sales Ledger]
FROM F03B11
SELECT SUM(GBAPYC + GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 +
GBAN06 + GBAN07 + GBAN08 + GBAN09 + GBAN10 + GBAN11 + GBAN12)/100
AS GL
FROM F0902
WHERE (GBAID = '00667809') AND (GBFY = 3)
I would like to take this a step further and deduce the difference
between the two. However I?m not sure how this should be done.
Any help would be most welcome.
Sam
?new person on windows 2000?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Use sub queries thus:
select [Sales Ledger] - gl as difference
from
(SELECT SUM(RPAAP / 100) AS [Sales Ledger] FROM F03B11) Q1 join
(SELECT SUM(GBAPYC + GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 + GBAN06
+ GBAN07 + GBAN08 + GBAN09 + GBAN10 + GBAN11 + GBAN12)/100
AS GL
FROM F0902
WHERE (GBAID = '00667809') AND (GBFY = 3)) Q2
Assuming Q2 returns just the one row
"Sam G" <moby@.spamhole.com> wrote in message
news:%23aCWsvTnDHA.2628@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I have a query that adds up two tables as below:
> SELECT SUM(RPAAP / 100) AS [Sales Ledger]
> FROM F03B11
> SELECT SUM(GBAPYC + GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 +
> GBAN06 + GBAN07 + GBAN08 + GBAN09 + GBAN10 + GBAN11 + GBAN12)/100
> AS GL
> FROM F0902
> WHERE (GBAID = '00667809') AND (GBFY = 3)
> I would like to take this a step further and deduce the difference
> between the two. However I'm not sure how this should be done.
> Any help would be most welcome.
> Sam
> "new person on windows 2000"
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003|||SELECT SUM(RPAAP / 100) AS [Sales Ledger] -
(SELECT SUM(GBAPYC + GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 +
GBAN06 + GBAN07 + GBAN08 + GBAN09 + GBAN10 + GBAN11 + GBAN12)/100
AS GL
FROM F0902
WHERE (GBAID = '00667809') AND (GBFY = 3))
FROM F03B11
Hope this helps
Wayne Snyder, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sam G" <moby@.spamhole.com> wrote in message
news:%23aCWsvTnDHA.2628@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I have a query that adds up two tables as below:
> SELECT SUM(RPAAP / 100) AS [Sales Ledger]
> FROM F03B11
> SELECT SUM(GBAPYC + GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 +
> GBAN06 + GBAN07 + GBAN08 + GBAN09 + GBAN10 + GBAN11 + GBAN12)/100
> AS GL
> FROM F0902
> WHERE (GBAID = '00667809') AND (GBFY = 3)
> I would like to take this a step further and deduce the difference
> between the two. However I'm not sure how this should be done.
> Any help would be most welcome.
> Sam
> "new person on windows 2000"
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
Friday, March 9, 2012
Dual CPU Machine and SQL 7.0
We have a complex query that runs in 3-5 seconds on a single CPU machine. When we install the query on a Dual CPU machine, Optimizer selects a totally different execution plan that causes table scans and takes about 45 - 360 seconds to execute.
Any ideas on what to do?
ThanksWhen was the last time you updated statistics, re-built indexes and re-compiled your sps on the dual box?|||We have performed several analyses and traces on the system. We tore apart the Stored Procedure (and ended up re-assembling it the same way).
We dropped and rebuilt all indexes. Update Stats and recompile runs daily on all DBs.
We determined that on dual cpu machines, SQL Optimizer decides to use a bad Execution Path that includes Table Scans (thus the 30-40 minutes).
The data is the same across all the machines, as are the indexes, primary keys, security permissions etc.
We've eliminated flags/settings differences within SQL itself.
We eliminated the raid configuration as on single-cpu machines it runs on any raid config and won't run on dual-cpu any raid or non raid config.
When we pop out one of the CPUs, then the stored procedure runs in 3-5 seconds.|||I'm not quit sure that it's a because of a dual processor. Our development machine and UAT machine are single processors. Our Production and Reporting servers are dual processors and they created the same query plan as those on development and UAT. There is this one instance, about a month ago where a production stored procedure created a different query plan from development, UAT and the Reporting server. But there was one thing different and that was the Production server's indexes where created as CONTRAINTS, whereas on the other machines they where created with CREATE INDEX. The reason Production had to change was because transactional replication requires keys to be defined via PRIMARY KEY constraints. So to rectify the problem I coded tables in the FROM statement in the order of query optimization and adding the FORCE ORDER option.
This cleared up the problem. I never use HINTS or any of those FORCE options before, this is my first time. I'm not one to tamper with query plans, however in this one instance it helped.|||Thanks for the idea, but we have already tried the FORCE and hints options and Optimizer still overrides them. The data is the same across all the machines, as are the indexes, primary keys, security permissions etc.|||Dumb question here but is your hardware and os configured for one or two cpus?
I talked to one of our surver guys and he said you can't just reomve a cpu, reboot and expect NT to run properly.
In fact based on what you said I am wondering if NT was installed for one processor while the hardware is configured for 2 cpus!|||Thanks for the suggestion. We tried 2 different dual-cpu machines.
When configuring the machines both were wiped and WIN 2K SP2+ and SQL 7.0 SP3 were re-installed for a 2-cpu environment.
One machine (Dev) is AN HP LH4R w/Dual Pentium II Xeon 400 mhz CPUs. 2Ghz mem w/HP Netraid controller running a Raid 5 array.
The other machine (Production) is a IX Systems Tyan Motherboard Thunder model LES2510 with 2 Pentium III 1Ghz CPUs. Symbios SCSI Controller hooked to Infotran Raid Controller Model 3102 running Raid 5 array.
When the machines were taken back to 1 CPU, WIN 2K and SQL were again wiped and re-installed.|||Another "Is it Plugged in?" Question...
Are the settings correct for Paralellism in the 'Properties'
under your server in EM... Minimum query plan threshold setting...
defaults at 5 seconds (cost estimate)... for multi-CPU machines|||Hi all,
If the machine can be restarted - then boot to a single processor mode and run the tests...
just a shot...
take care
tony|||We have an sql application that runs perfecly on single processor machines. we recently installed on a dual processor capable server with only one Xeon 1.8 Ghz processor, windows2000 server. Suprisingly we find the sql performance has degraded dramatically( when compared to running it on 1 Ghz, single processpor)
I went through a number of message postings and detrmined that a number of other people have seen performace degradation or at least no increase in performance. Here are the links, the common thread is all of them have xeon processors
Wondering if this is a setup issue on the server or ....
http://216.239.33.100/search?q=cache:GokRMOJ5XtQC:dbforums.com/t363432.html+CONFIGURING+DUAL+PROCESSOR+SQL&hl=en&ie=UTF-8
http://webforums.sybase.com/nntp/nd000049.nsf/85255e6f0052055e85255d7f005ed8bc/dac80f4be40abe7352192d6d624343b1?OpenDocument
http://www.sqlmag.com/Forums/messageview.cfm?catid=5&threadid=4383
http://www.sqlmag.com/Forums/messageview.cfm?catid=5&threadid=5970
http://www.sqlmag.com/Forums/messageview.cfm?catid=5&threadid=4466
http://www.sqlmag.com/Forums/messageview.cfm?catid=22&threadid=3358
http://www.winnetmag.com/Forums/Application/Thread.cfm?CFID=19669352&CFTOKEN=65475496&CFApp=70&Thread_ID=88250|||We still haven't fixed the problem. We're currently trying different tests with setting the parallelism. I'll investigate the latest suggestions and incorporate them into our tests.
Thanks for all your help.
Any ideas on what to do?
ThanksWhen was the last time you updated statistics, re-built indexes and re-compiled your sps on the dual box?|||We have performed several analyses and traces on the system. We tore apart the Stored Procedure (and ended up re-assembling it the same way).
We dropped and rebuilt all indexes. Update Stats and recompile runs daily on all DBs.
We determined that on dual cpu machines, SQL Optimizer decides to use a bad Execution Path that includes Table Scans (thus the 30-40 minutes).
The data is the same across all the machines, as are the indexes, primary keys, security permissions etc.
We've eliminated flags/settings differences within SQL itself.
We eliminated the raid configuration as on single-cpu machines it runs on any raid config and won't run on dual-cpu any raid or non raid config.
When we pop out one of the CPUs, then the stored procedure runs in 3-5 seconds.|||I'm not quit sure that it's a because of a dual processor. Our development machine and UAT machine are single processors. Our Production and Reporting servers are dual processors and they created the same query plan as those on development and UAT. There is this one instance, about a month ago where a production stored procedure created a different query plan from development, UAT and the Reporting server. But there was one thing different and that was the Production server's indexes where created as CONTRAINTS, whereas on the other machines they where created with CREATE INDEX. The reason Production had to change was because transactional replication requires keys to be defined via PRIMARY KEY constraints. So to rectify the problem I coded tables in the FROM statement in the order of query optimization and adding the FORCE ORDER option.
This cleared up the problem. I never use HINTS or any of those FORCE options before, this is my first time. I'm not one to tamper with query plans, however in this one instance it helped.|||Thanks for the idea, but we have already tried the FORCE and hints options and Optimizer still overrides them. The data is the same across all the machines, as are the indexes, primary keys, security permissions etc.|||Dumb question here but is your hardware and os configured for one or two cpus?
I talked to one of our surver guys and he said you can't just reomve a cpu, reboot and expect NT to run properly.
In fact based on what you said I am wondering if NT was installed for one processor while the hardware is configured for 2 cpus!|||Thanks for the suggestion. We tried 2 different dual-cpu machines.
When configuring the machines both were wiped and WIN 2K SP2+ and SQL 7.0 SP3 were re-installed for a 2-cpu environment.
One machine (Dev) is AN HP LH4R w/Dual Pentium II Xeon 400 mhz CPUs. 2Ghz mem w/HP Netraid controller running a Raid 5 array.
The other machine (Production) is a IX Systems Tyan Motherboard Thunder model LES2510 with 2 Pentium III 1Ghz CPUs. Symbios SCSI Controller hooked to Infotran Raid Controller Model 3102 running Raid 5 array.
When the machines were taken back to 1 CPU, WIN 2K and SQL were again wiped and re-installed.|||Another "Is it Plugged in?" Question...
Are the settings correct for Paralellism in the 'Properties'
under your server in EM... Minimum query plan threshold setting...
defaults at 5 seconds (cost estimate)... for multi-CPU machines|||Hi all,
If the machine can be restarted - then boot to a single processor mode and run the tests...
just a shot...
take care
tony|||We have an sql application that runs perfecly on single processor machines. we recently installed on a dual processor capable server with only one Xeon 1.8 Ghz processor, windows2000 server. Suprisingly we find the sql performance has degraded dramatically( when compared to running it on 1 Ghz, single processpor)
I went through a number of message postings and detrmined that a number of other people have seen performace degradation or at least no increase in performance. Here are the links, the common thread is all of them have xeon processors
Wondering if this is a setup issue on the server or ....
http://216.239.33.100/search?q=cache:GokRMOJ5XtQC:dbforums.com/t363432.html+CONFIGURING+DUAL+PROCESSOR+SQL&hl=en&ie=UTF-8
http://webforums.sybase.com/nntp/nd000049.nsf/85255e6f0052055e85255d7f005ed8bc/dac80f4be40abe7352192d6d624343b1?OpenDocument
http://www.sqlmag.com/Forums/messageview.cfm?catid=5&threadid=4383
http://www.sqlmag.com/Forums/messageview.cfm?catid=5&threadid=5970
http://www.sqlmag.com/Forums/messageview.cfm?catid=5&threadid=4466
http://www.sqlmag.com/Forums/messageview.cfm?catid=22&threadid=3358
http://www.winnetmag.com/Forums/Application/Thread.cfm?CFID=19669352&CFTOKEN=65475496&CFApp=70&Thread_ID=88250|||We still haven't fixed the problem. We're currently trying different tests with setting the parallelism. I'll investigate the latest suggestions and incorporate them into our tests.
Thanks for all your help.
Dual Core CPUs and Parallelism
Hi,
In some articles, it is mentioned that Hyperthreaded processors might have
negative impact on query performance and the SQL Server must be configured
to use actual number of CPUs for parallelism. Even once I encountered this
situation and problem eliminated by using MAXDOP option for the query.
Is it true for Dual Core processors as well?
Thanks in advance,
Leila
On Wed, 14 Nov 2007 00:40:14 +0330, "Leila" <Leilas@.hotpop.com> wrote:
>In some articles, it is mentioned that Hyperthreaded processors might have
>negative impact on query performance and the SQL Server must be configured
>to use actual number of CPUs for parallelism. Even once I encountered this
>situation and problem eliminated by using MAXDOP option for the query.
>Is it true for Dual Core processors as well?
No, it is not true for multi-core processors. Multi-core processors
have an actual CPU for each core. Hyperthreading occurred on a single
CPU and didn't really do much.
Roy Harvey
Beacon Falls, CT
|||Leila,
This article only mentions hyperthreading:
http://support.microsoft.com/default.aspx/kb/322385 I understand that
dual-core is not the same as hyperthreading. This article applies the
principle to multi-core. Be sure to read the comments that follow for a
fuller picture:
http://blogs.msdn.com/sqltips/archive/2005/09/14/466387.aspx
So, they appear to be saying multi-core is not the same as hyperthreading,
but a little modesty in the parallelism is a good idea. (In other posts (if
you google around) you can find people who feel strongly that OLTP should
never use parallelism.)
This article http://www.sqlmag.com/Articles/ArticleID/97044/97044.html?Ad=1
by Andrew Kelly says, in part, "Typically, online transaction processing
(OLTP) systems benefit more from a lower degree of parallelism, and
reporting systems benefit more from higher ..."
FWIW,
RLF
"Leila" <Leilas@.hotpop.com> wrote in message
news:etHHemjJIHA.4272@.TK2MSFTNGP06.phx.gbl...
> Hi,
> In some articles, it is mentioned that Hyperthreaded processors might have
> negative impact on query performance and the SQL Server must be configured
> to use actual number of CPUs for parallelism. Even once I encountered this
> situation and problem eliminated by using MAXDOP option for the query.
> Is it true for Dual Core processors as well?
> Thanks in advance,
> Leila
>
|||Hyperthreading is not the same multi-core technology. Hyperthreading fakes
multi-processors, is a dated technology, and wasn't (still isn't) good for an
inherently multi-threaded app such as SQL Server.
But when it comes to query parallelism, hyperthreading and multi-core will
pretty much give you the same since SQL Server operates on logical
processors. So if there is a need for you to restrict MAXDOP under
hyperthreading, you need to restrict MAXDOP with multi-cores.
Linchi
"Leila" wrote:
> Hi,
> In some articles, it is mentioned that Hyperthreaded processors might have
> negative impact on query performance and the SQL Server must be configured
> to use actual number of CPUs for parallelism. Even once I encountered this
> situation and problem eliminated by using MAXDOP option for the query.
> Is it true for Dual Core processors as well?
> Thanks in advance,
> Leila
>
>
|||To add to Russell's comment about OLTP systems take a look at section 2 of
this link saying "Given the high volumes of OLTP, parallel queries usually
reduce OLTP throughput and should be avoided"
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/oltp-performance-issues.mspx
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:
> Leila,
> This article only mentions hyperthreading:
> http://support.microsoft.com/default.aspx/kb/322385 I understand that
> dual-core is not the same as hyperthreading. This article applies the
> principle to multi-core. Be sure to read the comments that follow for a
> fuller picture:
> http://blogs.msdn.com/sqltips/archive/2005/09/14/466387.aspx
> So, they appear to be saying multi-core is not the same as hyperthreading,
> but a little modesty in the parallelism is a good idea. (In other posts (if
> you google around) you can find people who feel strongly that OLTP should
> never use parallelism.)
> This article http://www.sqlmag.com/Articles/ArticleID/97044/97044.html?Ad=1
> by Andrew Kelly says, in part, "Typically, online transaction processing
> (OLTP) systems benefit more from a lower degree of parallelism, and
> reporting systems benefit more from higher ..."
> FWIW,
> RLF
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:etHHemjJIHA.4272@.TK2MSFTNGP06.phx.gbl...
>
>
|||Roy Harvey (SQL Server MVP) wrote:
> No, it is not true for multi-core processors. Multi-core processors
> have an actual CPU for each core. Hyperthreading occurred on a single
> CPU and didn't really do much.
>
But in multi-core processors cores share caches and I/O -ports. In
arithmetic tasks this does not matter, but what about DBMS servers, that
use both memory and I/O quite a lot? I guess dual processor is better
than dual core, for example.
Arto Viitanen
|||Paul Randall has a very good post about 2 dual-core chips vs. 4 single-core
chips. You can find his blog at www.sqlskills.com. The ultimate answer is
"It Depends", but he delves into several scanrios during his post.
Rick Heiges
SQL Server MVP
"Arto V Viitanen" <arto.viitanen@.pp4.inet.fi> wrote in message
news:6yH_i.242$Ru3.9@.read3.inet.fi...
> Roy Harvey (SQL Server MVP) wrote:
>
> But in multi-core processors cores share caches and I/O -ports. In
> arithmetic tasks this does not matter, but what about DBMS servers, that
> use both memory and I/O quite a lot? I guess dual processor is better
> than dual core, for example.
> --
> Arto Viitanen
|||On Wed, 14 Nov 2007 18:57:06 GMT, Arto V Viitanen
<arto.viitanen@.pp4.inet.fi> wrote:
>But in multi-core processors cores share caches and I/O -ports. In
>arithmetic tasks this does not matter, but what about DBMS servers, that
>use both memory and I/O quite a lot? I guess dual processor is better
>than dual core, for example.
No question that multi-core processors are a compromise, but the
original question compared a dual core to a single core with
hyperthreading - and they really are not comparable.
Roy Harvey
Beacon Falls, CT
In some articles, it is mentioned that Hyperthreaded processors might have
negative impact on query performance and the SQL Server must be configured
to use actual number of CPUs for parallelism. Even once I encountered this
situation and problem eliminated by using MAXDOP option for the query.
Is it true for Dual Core processors as well?
Thanks in advance,
Leila
On Wed, 14 Nov 2007 00:40:14 +0330, "Leila" <Leilas@.hotpop.com> wrote:
>In some articles, it is mentioned that Hyperthreaded processors might have
>negative impact on query performance and the SQL Server must be configured
>to use actual number of CPUs for parallelism. Even once I encountered this
>situation and problem eliminated by using MAXDOP option for the query.
>Is it true for Dual Core processors as well?
No, it is not true for multi-core processors. Multi-core processors
have an actual CPU for each core. Hyperthreading occurred on a single
CPU and didn't really do much.
Roy Harvey
Beacon Falls, CT
|||Leila,
This article only mentions hyperthreading:
http://support.microsoft.com/default.aspx/kb/322385 I understand that
dual-core is not the same as hyperthreading. This article applies the
principle to multi-core. Be sure to read the comments that follow for a
fuller picture:
http://blogs.msdn.com/sqltips/archive/2005/09/14/466387.aspx
So, they appear to be saying multi-core is not the same as hyperthreading,
but a little modesty in the parallelism is a good idea. (In other posts (if
you google around) you can find people who feel strongly that OLTP should
never use parallelism.)
This article http://www.sqlmag.com/Articles/ArticleID/97044/97044.html?Ad=1
by Andrew Kelly says, in part, "Typically, online transaction processing
(OLTP) systems benefit more from a lower degree of parallelism, and
reporting systems benefit more from higher ..."
FWIW,
RLF
"Leila" <Leilas@.hotpop.com> wrote in message
news:etHHemjJIHA.4272@.TK2MSFTNGP06.phx.gbl...
> Hi,
> In some articles, it is mentioned that Hyperthreaded processors might have
> negative impact on query performance and the SQL Server must be configured
> to use actual number of CPUs for parallelism. Even once I encountered this
> situation and problem eliminated by using MAXDOP option for the query.
> Is it true for Dual Core processors as well?
> Thanks in advance,
> Leila
>
|||Hyperthreading is not the same multi-core technology. Hyperthreading fakes
multi-processors, is a dated technology, and wasn't (still isn't) good for an
inherently multi-threaded app such as SQL Server.
But when it comes to query parallelism, hyperthreading and multi-core will
pretty much give you the same since SQL Server operates on logical
processors. So if there is a need for you to restrict MAXDOP under
hyperthreading, you need to restrict MAXDOP with multi-cores.
Linchi
"Leila" wrote:
> Hi,
> In some articles, it is mentioned that Hyperthreaded processors might have
> negative impact on query performance and the SQL Server must be configured
> to use actual number of CPUs for parallelism. Even once I encountered this
> situation and problem eliminated by using MAXDOP option for the query.
> Is it true for Dual Core processors as well?
> Thanks in advance,
> Leila
>
>
|||To add to Russell's comment about OLTP systems take a look at section 2 of
this link saying "Given the high volumes of OLTP, parallel queries usually
reduce OLTP throughput and should be avoided"
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/oltp-performance-issues.mspx
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:
> Leila,
> This article only mentions hyperthreading:
> http://support.microsoft.com/default.aspx/kb/322385 I understand that
> dual-core is not the same as hyperthreading. This article applies the
> principle to multi-core. Be sure to read the comments that follow for a
> fuller picture:
> http://blogs.msdn.com/sqltips/archive/2005/09/14/466387.aspx
> So, they appear to be saying multi-core is not the same as hyperthreading,
> but a little modesty in the parallelism is a good idea. (In other posts (if
> you google around) you can find people who feel strongly that OLTP should
> never use parallelism.)
> This article http://www.sqlmag.com/Articles/ArticleID/97044/97044.html?Ad=1
> by Andrew Kelly says, in part, "Typically, online transaction processing
> (OLTP) systems benefit more from a lower degree of parallelism, and
> reporting systems benefit more from higher ..."
> FWIW,
> RLF
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:etHHemjJIHA.4272@.TK2MSFTNGP06.phx.gbl...
>
>
|||Roy Harvey (SQL Server MVP) wrote:
> No, it is not true for multi-core processors. Multi-core processors
> have an actual CPU for each core. Hyperthreading occurred on a single
> CPU and didn't really do much.
>
But in multi-core processors cores share caches and I/O -ports. In
arithmetic tasks this does not matter, but what about DBMS servers, that
use both memory and I/O quite a lot? I guess dual processor is better
than dual core, for example.
Arto Viitanen
|||Paul Randall has a very good post about 2 dual-core chips vs. 4 single-core
chips. You can find his blog at www.sqlskills.com. The ultimate answer is
"It Depends", but he delves into several scanrios during his post.
Rick Heiges
SQL Server MVP
"Arto V Viitanen" <arto.viitanen@.pp4.inet.fi> wrote in message
news:6yH_i.242$Ru3.9@.read3.inet.fi...
> Roy Harvey (SQL Server MVP) wrote:
>
> But in multi-core processors cores share caches and I/O -ports. In
> arithmetic tasks this does not matter, but what about DBMS servers, that
> use both memory and I/O quite a lot? I guess dual processor is better
> than dual core, for example.
> --
> Arto Viitanen
|||On Wed, 14 Nov 2007 18:57:06 GMT, Arto V Viitanen
<arto.viitanen@.pp4.inet.fi> wrote:
>But in multi-core processors cores share caches and I/O -ports. In
>arithmetic tasks this does not matter, but what about DBMS servers, that
>use both memory and I/O quite a lot? I guess dual processor is better
>than dual core, for example.
No question that multi-core processors are a compromise, but the
original question compared a dual core to a single core with
hyperthreading - and they really are not comparable.
Roy Harvey
Beacon Falls, CT
Dual Core CPUs and Parallelism
Hi,
In some articles, it is mentioned that Hyperthreaded processors might have
negative impact on query performance and the SQL Server must be configured
to use actual number of CPUs for parallelism. Even once I encountered this
situation and problem eliminated by using MAXDOP option for the query.
Is it true for Dual Core processors as well?
Thanks in advance,
LeilaOn Wed, 14 Nov 2007 00:40:14 +0330, "Leila" <Leilas@.hotpop.com> wrote:
>In some articles, it is mentioned that Hyperthreaded processors might have
>negative impact on query performance and the SQL Server must be configured
>to use actual number of CPUs for parallelism. Even once I encountered this
>situation and problem eliminated by using MAXDOP option for the query.
>Is it true for Dual Core processors as well?
No, it is not true for multi-core processors. Multi-core processors
have an actual CPU for each core. Hyperthreading occurred on a single
CPU and didn't really do much.
Roy Harvey
Beacon Falls, CT|||Leila,
This article only mentions hyperthreading:
http://support.microsoft.com/default.aspx/kb/322385 I understand that
dual-core is not the same as hyperthreading. This article applies the
principle to multi-core. Be sure to read the comments that follow for a
fuller picture:
http://blogs.msdn.com/sqltips/archi.../14/466387.aspx
So, they appear to be saying multi-core is not the same as hyperthreading,
but a little modesty in the parallelism is a good idea. (In other posts (if
you google around) you can find people who feel strongly that OLTP should
never use parallelism.)
This article http://www.sqlmag.com/Articles/Arti...97044.html?Ad=1
by Andrew Kelly says, in part, "Typically, online transaction processing
(OLTP) systems benefit more from a lower degree of parallelism, and
reporting systems benefit more from higher ..."
FWIW,
RLF
"Leila" <Leilas@.hotpop.com> wrote in message
news:etHHemjJIHA.4272@.TK2MSFTNGP06.phx.gbl...
> Hi,
> In some articles, it is mentioned that Hyperthreaded processors might have
> negative impact on query performance and the SQL Server must be configured
> to use actual number of CPUs for parallelism. Even once I encountered this
> situation and problem eliminated by using MAXDOP option for the query.
> Is it true for Dual Core processors as well?
> Thanks in advance,
> Leila
>|||Hyperthreading is not the same multi-core technology. Hyperthreading fakes
multi-processors, is a dated technology, and wasn't (still isn't) good for a
n
inherently multi-threaded app such as SQL Server.
But when it comes to query parallelism, hyperthreading and multi-core will
pretty much give you the same since SQL Server operates on logical
processors. So if there is a need for you to restrict MAXDOP under
hyperthreading, you need to restrict MAXDOP with multi-cores.
Linchi
"Leila" wrote:
> Hi,
> In some articles, it is mentioned that Hyperthreaded processors might have
> negative impact on query performance and the SQL Server must be configured
> to use actual number of CPUs for parallelism. Even once I encountered this
> situation and problem eliminated by using MAXDOP option for the query.
> Is it true for Dual Core processors as well?
> Thanks in advance,
> Leila
>
>|||To add to Russell's comment about OLTP systems take a look at section 2 of
this link saying "Given the high volumes of OLTP, parallel queries usually
reduce OLTP throughput and should be avoided"
http://www.microsoft.com/technet/pr...br />
ues.mspx
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:
> Leila,
> This article only mentions hyperthreading:
> http://support.microsoft.com/default.aspx/kb/322385 I understand that
> dual-core is not the same as hyperthreading. This article applies the
> principle to multi-core. Be sure to read the comments that follow for a
> fuller picture:
> http://blogs.msdn.com/sqltips/archi.../14/466387.aspx
> So, they appear to be saying multi-core is not the same as hyperthreading,
> but a little modesty in the parallelism is a good idea. (In other posts (
if
> you google around) you can find people who feel strongly that OLTP should
> never use parallelism.)
> This article [url]http://www.sqlmag.com/Articles/ArticleID/97044/97044.html?Ad=1[/url
]
> by Andrew Kelly says, in part, "Typically, online transaction processing
> (OLTP) systems benefit more from a lower degree of parallelism, and
> reporting systems benefit more from higher ..."
> FWIW,
> RLF
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:etHHemjJIHA.4272@.TK2MSFTNGP06.phx.gbl...
>
>|||Roy Harvey (SQL Server MVP) wrote:
> No, it is not true for multi-core processors. Multi-core processors
> have an actual CPU for each core. Hyperthreading occurred on a single
> CPU and didn't really do much.
>
But in multi-core processors cores share caches and I/O -ports. In
arithmetic tasks this does not matter, but what about DBMS servers, that
use both memory and I/O quite a lot? I guess dual processor is better
than dual core, for example.
Arto Viitanen|||Paul Randall has a very good post about 2 dual-core chips vs. 4 single-core
chips. You can find his blog at www.sqlskills.com. The ultimate answer is
"It Depends", but he delves into several scanrios during his post.
Rick Heiges
SQL Server MVP
"Arto V Viitanen" <arto.viitanen@.pp4.inet.fi> wrote in message
news:6yH_i.242$Ru3.9@.read3.inet.fi...
> Roy Harvey (SQL Server MVP) wrote:
>
> But in multi-core processors cores share caches and I/O -ports. In
> arithmetic tasks this does not matter, but what about DBMS servers, that
> use both memory and I/O quite a lot? I guess dual processor is better
> than dual core, for example.
> --
> Arto Viitanen|||On Wed, 14 Nov 2007 18:57:06 GMT, Arto V Viitanen
<arto.viitanen@.pp4.inet.fi> wrote:
>But in multi-core processors cores share caches and I/O -ports. In
>arithmetic tasks this does not matter, but what about DBMS servers, that
>use both memory and I/O quite a lot? I guess dual processor is better
>than dual core, for example.
No question that multi-core processors are a compromise, but the
original question compared a dual core to a single core with
hyperthreading - and they really are not comparable.
Roy Harvey
Beacon Falls, CT
In some articles, it is mentioned that Hyperthreaded processors might have
negative impact on query performance and the SQL Server must be configured
to use actual number of CPUs for parallelism. Even once I encountered this
situation and problem eliminated by using MAXDOP option for the query.
Is it true for Dual Core processors as well?
Thanks in advance,
LeilaOn Wed, 14 Nov 2007 00:40:14 +0330, "Leila" <Leilas@.hotpop.com> wrote:
>In some articles, it is mentioned that Hyperthreaded processors might have
>negative impact on query performance and the SQL Server must be configured
>to use actual number of CPUs for parallelism. Even once I encountered this
>situation and problem eliminated by using MAXDOP option for the query.
>Is it true for Dual Core processors as well?
No, it is not true for multi-core processors. Multi-core processors
have an actual CPU for each core. Hyperthreading occurred on a single
CPU and didn't really do much.
Roy Harvey
Beacon Falls, CT|||Leila,
This article only mentions hyperthreading:
http://support.microsoft.com/default.aspx/kb/322385 I understand that
dual-core is not the same as hyperthreading. This article applies the
principle to multi-core. Be sure to read the comments that follow for a
fuller picture:
http://blogs.msdn.com/sqltips/archi.../14/466387.aspx
So, they appear to be saying multi-core is not the same as hyperthreading,
but a little modesty in the parallelism is a good idea. (In other posts (if
you google around) you can find people who feel strongly that OLTP should
never use parallelism.)
This article http://www.sqlmag.com/Articles/Arti...97044.html?Ad=1
by Andrew Kelly says, in part, "Typically, online transaction processing
(OLTP) systems benefit more from a lower degree of parallelism, and
reporting systems benefit more from higher ..."
FWIW,
RLF
"Leila" <Leilas@.hotpop.com> wrote in message
news:etHHemjJIHA.4272@.TK2MSFTNGP06.phx.gbl...
> Hi,
> In some articles, it is mentioned that Hyperthreaded processors might have
> negative impact on query performance and the SQL Server must be configured
> to use actual number of CPUs for parallelism. Even once I encountered this
> situation and problem eliminated by using MAXDOP option for the query.
> Is it true for Dual Core processors as well?
> Thanks in advance,
> Leila
>|||Hyperthreading is not the same multi-core technology. Hyperthreading fakes
multi-processors, is a dated technology, and wasn't (still isn't) good for a
n
inherently multi-threaded app such as SQL Server.
But when it comes to query parallelism, hyperthreading and multi-core will
pretty much give you the same since SQL Server operates on logical
processors. So if there is a need for you to restrict MAXDOP under
hyperthreading, you need to restrict MAXDOP with multi-cores.
Linchi
"Leila" wrote:
> Hi,
> In some articles, it is mentioned that Hyperthreaded processors might have
> negative impact on query performance and the SQL Server must be configured
> to use actual number of CPUs for parallelism. Even once I encountered this
> situation and problem eliminated by using MAXDOP option for the query.
> Is it true for Dual Core processors as well?
> Thanks in advance,
> Leila
>
>|||To add to Russell's comment about OLTP systems take a look at section 2 of
this link saying "Given the high volumes of OLTP, parallel queries usually
reduce OLTP throughput and should be avoided"
http://www.microsoft.com/technet/pr...br />
ues.mspx
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:
> Leila,
> This article only mentions hyperthreading:
> http://support.microsoft.com/default.aspx/kb/322385 I understand that
> dual-core is not the same as hyperthreading. This article applies the
> principle to multi-core. Be sure to read the comments that follow for a
> fuller picture:
> http://blogs.msdn.com/sqltips/archi.../14/466387.aspx
> So, they appear to be saying multi-core is not the same as hyperthreading,
> but a little modesty in the parallelism is a good idea. (In other posts (
if
> you google around) you can find people who feel strongly that OLTP should
> never use parallelism.)
> This article [url]http://www.sqlmag.com/Articles/ArticleID/97044/97044.html?Ad=1[/url
]
> by Andrew Kelly says, in part, "Typically, online transaction processing
> (OLTP) systems benefit more from a lower degree of parallelism, and
> reporting systems benefit more from higher ..."
> FWIW,
> RLF
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:etHHemjJIHA.4272@.TK2MSFTNGP06.phx.gbl...
>
>|||Roy Harvey (SQL Server MVP) wrote:
> No, it is not true for multi-core processors. Multi-core processors
> have an actual CPU for each core. Hyperthreading occurred on a single
> CPU and didn't really do much.
>
But in multi-core processors cores share caches and I/O -ports. In
arithmetic tasks this does not matter, but what about DBMS servers, that
use both memory and I/O quite a lot? I guess dual processor is better
than dual core, for example.
Arto Viitanen|||Paul Randall has a very good post about 2 dual-core chips vs. 4 single-core
chips. You can find his blog at www.sqlskills.com. The ultimate answer is
"It Depends", but he delves into several scanrios during his post.
Rick Heiges
SQL Server MVP
"Arto V Viitanen" <arto.viitanen@.pp4.inet.fi> wrote in message
news:6yH_i.242$Ru3.9@.read3.inet.fi...
> Roy Harvey (SQL Server MVP) wrote:
>
> But in multi-core processors cores share caches and I/O -ports. In
> arithmetic tasks this does not matter, but what about DBMS servers, that
> use both memory and I/O quite a lot? I guess dual processor is better
> than dual core, for example.
> --
> Arto Viitanen|||On Wed, 14 Nov 2007 18:57:06 GMT, Arto V Viitanen
<arto.viitanen@.pp4.inet.fi> wrote:
>But in multi-core processors cores share caches and I/O -ports. In
>arithmetic tasks this does not matter, but what about DBMS servers, that
>use both memory and I/O quite a lot? I guess dual processor is better
>than dual core, for example.
No question that multi-core processors are a compromise, but the
original question compared a dual core to a single core with
hyperthreading - and they really are not comparable.
Roy Harvey
Beacon Falls, CT
Wednesday, March 7, 2012
Dual Core CPUs and Parallelism
Hi,
In some articles, it is mentioned that Hyperthreaded processors might have
negative impact on query performance and the SQL Server must be configured
to use actual number of CPUs for parallelism. Even once I encountered this
situation and problem eliminated by using MAXDOP option for the query.
Is it true for Dual Core processors as well?
Thanks in advance,
LeilaOn Wed, 14 Nov 2007 00:40:14 +0330, "Leila" <Leilas@.hotpop.com> wrote:
>In some articles, it is mentioned that Hyperthreaded processors might have
>negative impact on query performance and the SQL Server must be configured
>to use actual number of CPUs for parallelism. Even once I encountered this
>situation and problem eliminated by using MAXDOP option for the query.
>Is it true for Dual Core processors as well?
No, it is not true for multi-core processors. Multi-core processors
have an actual CPU for each core. Hyperthreading occurred on a single
CPU and didn't really do much.
Roy Harvey
Beacon Falls, CT|||Leila,
This article only mentions hyperthreading:
http://support.microsoft.com/default.aspx/kb/322385 I understand that
dual-core is not the same as hyperthreading. This article applies the
principle to multi-core. Be sure to read the comments that follow for a
fuller picture:
http://blogs.msdn.com/sqltips/archive/2005/09/14/466387.aspx
So, they appear to be saying multi-core is not the same as hyperthreading,
but a little modesty in the parallelism is a good idea. (In other posts (if
you google around) you can find people who feel strongly that OLTP should
never use parallelism.)
This article http://www.sqlmag.com/Articles/ArticleID/97044/97044.html?Ad=1
by Andrew Kelly says, in part, "Typically, online transaction processing
(OLTP) systems benefit more from a lower degree of parallelism, and
reporting systems benefit more from higher ..."
FWIW,
RLF
"Leila" <Leilas@.hotpop.com> wrote in message
news:etHHemjJIHA.4272@.TK2MSFTNGP06.phx.gbl...
> Hi,
> In some articles, it is mentioned that Hyperthreaded processors might have
> negative impact on query performance and the SQL Server must be configured
> to use actual number of CPUs for parallelism. Even once I encountered this
> situation and problem eliminated by using MAXDOP option for the query.
> Is it true for Dual Core processors as well?
> Thanks in advance,
> Leila
>|||Hyperthreading is not the same multi-core technology. Hyperthreading fakes
multi-processors, is a dated technology, and wasn't (still isn't) good for an
inherently multi-threaded app such as SQL Server.
But when it comes to query parallelism, hyperthreading and multi-core will
pretty much give you the same since SQL Server operates on logical
processors. So if there is a need for you to restrict MAXDOP under
hyperthreading, you need to restrict MAXDOP with multi-cores.
Linchi
"Leila" wrote:
> Hi,
> In some articles, it is mentioned that Hyperthreaded processors might have
> negative impact on query performance and the SQL Server must be configured
> to use actual number of CPUs for parallelism. Even once I encountered this
> situation and problem eliminated by using MAXDOP option for the query.
> Is it true for Dual Core processors as well?
> Thanks in advance,
> Leila
>
>|||To add to Russell's comment about OLTP systems take a look at section 2 of
this link saying "Given the high volumes of OLTP, parallel queries usually
reduce OLTP throughput and should be avoided"
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/oltp-performance-issues.mspx
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:
> Leila,
> This article only mentions hyperthreading:
> http://support.microsoft.com/default.aspx/kb/322385 I understand that
> dual-core is not the same as hyperthreading. This article applies the
> principle to multi-core. Be sure to read the comments that follow for a
> fuller picture:
> http://blogs.msdn.com/sqltips/archive/2005/09/14/466387.aspx
> So, they appear to be saying multi-core is not the same as hyperthreading,
> but a little modesty in the parallelism is a good idea. (In other posts (if
> you google around) you can find people who feel strongly that OLTP should
> never use parallelism.)
> This article http://www.sqlmag.com/Articles/ArticleID/97044/97044.html?Ad=1
> by Andrew Kelly says, in part, "Typically, online transaction processing
> (OLTP) systems benefit more from a lower degree of parallelism, and
> reporting systems benefit more from higher ..."
> FWIW,
> RLF
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:etHHemjJIHA.4272@.TK2MSFTNGP06.phx.gbl...
> > Hi,
> > In some articles, it is mentioned that Hyperthreaded processors might have
> > negative impact on query performance and the SQL Server must be configured
> > to use actual number of CPUs for parallelism. Even once I encountered this
> > situation and problem eliminated by using MAXDOP option for the query.
> > Is it true for Dual Core processors as well?
> > Thanks in advance,
> > Leila
> >
>
>|||Roy Harvey (SQL Server MVP) wrote:
> No, it is not true for multi-core processors. Multi-core processors
> have an actual CPU for each core. Hyperthreading occurred on a single
> CPU and didn't really do much.
>
But in multi-core processors cores share caches and I/O -ports. In
arithmetic tasks this does not matter, but what about DBMS servers, that
use both memory and I/O quite a lot? I guess dual processor is better
than dual core, for example.
--
Arto Viitanen|||Paul Randall has a very good post about 2 dual-core chips vs. 4 single-core
chips. You can find his blog at www.sqlskills.com. The ultimate answer is
"It Depends", but he delves into several scanrios during his post.
Rick Heiges
SQL Server MVP
"Arto V Viitanen" <arto.viitanen@.pp4.inet.fi> wrote in message
news:6yH_i.242$Ru3.9@.read3.inet.fi...
> Roy Harvey (SQL Server MVP) wrote:
>> No, it is not true for multi-core processors. Multi-core processors
>> have an actual CPU for each core. Hyperthreading occurred on a single
>> CPU and didn't really do much.
> But in multi-core processors cores share caches and I/O -ports. In
> arithmetic tasks this does not matter, but what about DBMS servers, that
> use both memory and I/O quite a lot? I guess dual processor is better
> than dual core, for example.
> --
> Arto Viitanen|||On Wed, 14 Nov 2007 18:57:06 GMT, Arto V Viitanen
<arto.viitanen@.pp4.inet.fi> wrote:
>But in multi-core processors cores share caches and I/O -ports. In
>arithmetic tasks this does not matter, but what about DBMS servers, that
>use both memory and I/O quite a lot? I guess dual processor is better
>than dual core, for example.
No question that multi-core processors are a compromise, but the
original question compared a dual core to a single core with
hyperthreading - and they really are not comparable.
Roy Harvey
Beacon Falls, CT
In some articles, it is mentioned that Hyperthreaded processors might have
negative impact on query performance and the SQL Server must be configured
to use actual number of CPUs for parallelism. Even once I encountered this
situation and problem eliminated by using MAXDOP option for the query.
Is it true for Dual Core processors as well?
Thanks in advance,
LeilaOn Wed, 14 Nov 2007 00:40:14 +0330, "Leila" <Leilas@.hotpop.com> wrote:
>In some articles, it is mentioned that Hyperthreaded processors might have
>negative impact on query performance and the SQL Server must be configured
>to use actual number of CPUs for parallelism. Even once I encountered this
>situation and problem eliminated by using MAXDOP option for the query.
>Is it true for Dual Core processors as well?
No, it is not true for multi-core processors. Multi-core processors
have an actual CPU for each core. Hyperthreading occurred on a single
CPU and didn't really do much.
Roy Harvey
Beacon Falls, CT|||Leila,
This article only mentions hyperthreading:
http://support.microsoft.com/default.aspx/kb/322385 I understand that
dual-core is not the same as hyperthreading. This article applies the
principle to multi-core. Be sure to read the comments that follow for a
fuller picture:
http://blogs.msdn.com/sqltips/archive/2005/09/14/466387.aspx
So, they appear to be saying multi-core is not the same as hyperthreading,
but a little modesty in the parallelism is a good idea. (In other posts (if
you google around) you can find people who feel strongly that OLTP should
never use parallelism.)
This article http://www.sqlmag.com/Articles/ArticleID/97044/97044.html?Ad=1
by Andrew Kelly says, in part, "Typically, online transaction processing
(OLTP) systems benefit more from a lower degree of parallelism, and
reporting systems benefit more from higher ..."
FWIW,
RLF
"Leila" <Leilas@.hotpop.com> wrote in message
news:etHHemjJIHA.4272@.TK2MSFTNGP06.phx.gbl...
> Hi,
> In some articles, it is mentioned that Hyperthreaded processors might have
> negative impact on query performance and the SQL Server must be configured
> to use actual number of CPUs for parallelism. Even once I encountered this
> situation and problem eliminated by using MAXDOP option for the query.
> Is it true for Dual Core processors as well?
> Thanks in advance,
> Leila
>|||Hyperthreading is not the same multi-core technology. Hyperthreading fakes
multi-processors, is a dated technology, and wasn't (still isn't) good for an
inherently multi-threaded app such as SQL Server.
But when it comes to query parallelism, hyperthreading and multi-core will
pretty much give you the same since SQL Server operates on logical
processors. So if there is a need for you to restrict MAXDOP under
hyperthreading, you need to restrict MAXDOP with multi-cores.
Linchi
"Leila" wrote:
> Hi,
> In some articles, it is mentioned that Hyperthreaded processors might have
> negative impact on query performance and the SQL Server must be configured
> to use actual number of CPUs for parallelism. Even once I encountered this
> situation and problem eliminated by using MAXDOP option for the query.
> Is it true for Dual Core processors as well?
> Thanks in advance,
> Leila
>
>|||To add to Russell's comment about OLTP systems take a look at section 2 of
this link saying "Given the high volumes of OLTP, parallel queries usually
reduce OLTP throughput and should be avoided"
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/oltp-performance-issues.mspx
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:
> Leila,
> This article only mentions hyperthreading:
> http://support.microsoft.com/default.aspx/kb/322385 I understand that
> dual-core is not the same as hyperthreading. This article applies the
> principle to multi-core. Be sure to read the comments that follow for a
> fuller picture:
> http://blogs.msdn.com/sqltips/archive/2005/09/14/466387.aspx
> So, they appear to be saying multi-core is not the same as hyperthreading,
> but a little modesty in the parallelism is a good idea. (In other posts (if
> you google around) you can find people who feel strongly that OLTP should
> never use parallelism.)
> This article http://www.sqlmag.com/Articles/ArticleID/97044/97044.html?Ad=1
> by Andrew Kelly says, in part, "Typically, online transaction processing
> (OLTP) systems benefit more from a lower degree of parallelism, and
> reporting systems benefit more from higher ..."
> FWIW,
> RLF
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:etHHemjJIHA.4272@.TK2MSFTNGP06.phx.gbl...
> > Hi,
> > In some articles, it is mentioned that Hyperthreaded processors might have
> > negative impact on query performance and the SQL Server must be configured
> > to use actual number of CPUs for parallelism. Even once I encountered this
> > situation and problem eliminated by using MAXDOP option for the query.
> > Is it true for Dual Core processors as well?
> > Thanks in advance,
> > Leila
> >
>
>|||Roy Harvey (SQL Server MVP) wrote:
> No, it is not true for multi-core processors. Multi-core processors
> have an actual CPU for each core. Hyperthreading occurred on a single
> CPU and didn't really do much.
>
But in multi-core processors cores share caches and I/O -ports. In
arithmetic tasks this does not matter, but what about DBMS servers, that
use both memory and I/O quite a lot? I guess dual processor is better
than dual core, for example.
--
Arto Viitanen|||Paul Randall has a very good post about 2 dual-core chips vs. 4 single-core
chips. You can find his blog at www.sqlskills.com. The ultimate answer is
"It Depends", but he delves into several scanrios during his post.
Rick Heiges
SQL Server MVP
"Arto V Viitanen" <arto.viitanen@.pp4.inet.fi> wrote in message
news:6yH_i.242$Ru3.9@.read3.inet.fi...
> Roy Harvey (SQL Server MVP) wrote:
>> No, it is not true for multi-core processors. Multi-core processors
>> have an actual CPU for each core. Hyperthreading occurred on a single
>> CPU and didn't really do much.
> But in multi-core processors cores share caches and I/O -ports. In
> arithmetic tasks this does not matter, but what about DBMS servers, that
> use both memory and I/O quite a lot? I guess dual processor is better
> than dual core, for example.
> --
> Arto Viitanen|||On Wed, 14 Nov 2007 18:57:06 GMT, Arto V Viitanen
<arto.viitanen@.pp4.inet.fi> wrote:
>But in multi-core processors cores share caches and I/O -ports. In
>arithmetic tasks this does not matter, but what about DBMS servers, that
>use both memory and I/O quite a lot? I guess dual processor is better
>than dual core, for example.
No question that multi-core processors are a compromise, but the
original question compared a dual core to a single core with
hyperthreading - and they really are not comparable.
Roy Harvey
Beacon Falls, CT
dtsrun works from command prompt, but hangs from Query Analyzer
I have a dts that works from the command prompt, but hangs in Query Analyzer. Here's the code inside Query Analyzer:
exec master..xp_cmdshell 'dtsrun /S BFHSQL4 /N vhl_dts_14144b /E'
exec master..xp_cmdshell 'dtsrun /S BFHSQL4 /N vhl_dts_14144b /E'
From the command prompt, it's just:
dtsrun /S BFHSQL4 /N vhl_dts_14144b /E
Any ideas what's wrong? Thanks for your help!Did the dtsrun cause SQL Server hang or just Query Analyzer hang? Did you got any error message when executing the package from Query Analyzer? You need more information for troubleshooting: use SQL Profiler to respectively capture a trace when execute dtsrun to the same package from commandline and Query Analyzer.|||Thanks for your response. I guess hangs might not be the right word. There are no error messages. Query Analyzer just tries to execute the package forever. It takes less than a minute to run from the command prompt. I've let it run for 1/2 hour in Query Analyzer before cancelling the query, then it takes several minutes to cancel. Other packages run fine with the same syntax. I think the problem is with this particular package. The dts just runs an activeX script, as follows:
Function Main()
Dim fileName
fileName = "\\mypath\myExcelFile.xls"
dim excelObject
set excelObject = CreateObject("Excel.Application")
excelObject.workbooks.open fileName
excelObject.Quit
set excelObject = Nothing
Main = DTSTaskExecResult_Success
End Function
I'm not really familiar with SQL Profiler. Do you still think using it will be helpful for this scenario? Thanks again for your help.|||Turns out that there was code inside the Excel file that was trying to save a file to a path that didn't exist. I fixed the path and now everything works fine.
Sunday, February 26, 2012
DTSRun error
Hi,
I am trying to run the following command within my Query Analyzer and get the following:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '/'.
I found it: xp_cmdshell needs to be used from within Query Analyzer.|||
Good approach and Reply keep it up.
I am trying to run the following command within my Query Analyzer and get the following:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '/'.
The command is:
DTSRun /S fa314146 /U sa /P admin /N TXTFile_To_SQL
I tried using quotes around my optional parameters but it didn't help.
Thanks for you help.
Igor - isheyman139@.worldsavings.comI found it: xp_cmdshell needs to be used from within Query Analyzer.
Quote:
Originally Posted by isheyman
Hi,
I am trying to run the following command within my Query Analyzer and get the following:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '/'.
The command is:
DTSRun /S fa314146 /U sa /P admin /N TXTFile_To_SQL
I tried using quotes around my optional parameters but it didn't help.
Thanks for you help.
Igor - isheyman139@.worldsavings.com
I found it: xp_cmdshell needs to be used from within Query Analyzer.|||
Quote:
Originally Posted by isheyman
I found it: xp_cmdshell needs to be used from within Query Analyzer.
Good approach and Reply keep it up.
Subscribe to:
Posts (Atom)