Order by problem

Hello,

I have a table with a field called 'time' which is type char.

The data which goes into this field is 1am, 2am, 3am, 4am ,5am, 6am, 7am, 8am ,9am, 10am, 11am, 12noon, 1pm, 2pm, 3pm, 4pm,5pm, 6pm, 7pm, 8pm, 9pm, 10pm, 11pm, 12mid.

As users enter the data the order can go out of sequence, so I want to be able to sort the order to go as above for each day.

Is there a way of doing custom ordering by number, then letter of data in the same field.
or any other suggestions are welcome.

Thanks
GoongSuggestion: Store military time (00:00-23:00) and they will sort fine. Then, create a method in the UI that will display that value in a reasonable format.

Order By Primary Key

I've got a table with an a primary key of type 'int' (auto incrementing).
Were talking about millions of rows.
The name of this field is TempID.
I always want to sort by the TempID ascending...is that something that I
even need to specify?
When I query it in Query Analyzer it seems to always be sorted by the
primary key ascending.
Although this is what I want, will this ALWAYS be the case, or should I just
throw an order by in there for good measure?
Stored Proc:
--
@.numberOfRows int,
@.startingID int
set rowcount @.numberOfRows
Select * From tblList Where TempID > @.startingID
set rowcount 0
--
Will this always work how I expect or should I add the "Order By TempID"?
I am asking because I don't know if my Order By clause will slow it down.
I'm sure I wouldn't notice if it was .0000000002 ms slower, but this would
be good to know, especially if @.startingID = 4,000,000 or the @.numberOfRows
is very large.
I hope that all made sense.Read the BOL for more information under "Clustered Indexes":
"An index on the columns specified in the ORDER BY or GROUP BY clause
may remove the need for the Database Engine to sort the data, because
the rows are already sorted. This improves query performance."
Didi you create them as Clustered keys ?
HTH, Jens Suessmeyer.|||INeedADip wrote:
> I've got a table with an a primary key of type 'int' (auto
> incrementing). Were talking about millions of rows.
> The name of this field is TempID.
> I always want to sort by the TempID ascending...is that something
> that I even need to specify?
> When I query it in Query Analyzer it seems to always be sorted by the
> primary key ascending.
> Although this is what I want, will this ALWAYS be the case, or should
> I just throw an order by in there for good measure?
> Stored Proc:
> --
> @.numberOfRows int,
> @.startingID int
> set rowcount @.numberOfRows
> Select * From tblList Where TempID > @.startingID
> set rowcount 0
> --
> Will this always work how I expect or should I add the "Order By
> TempID"? I am asking because I don't know if my Order By clause will
> slow it
> down. I'm sure I wouldn't notice if it was .0000000002 ms slower, but
> this would be good to know, especially if @.startingID = 4,000,000 or
> the @.numberOfRows is very large.
> I hope that all made sense.
The only way to guarantee the order of results is with an ORDER BY
clause. If that column is your PK, you should already have a unique
index on the column. If it's clustered, it will help with sorting. Keep
in mind that sort operations are very costly. While the unique,
clustered index on the PK column might eliminate the need for the sort,
it is no guarantee. You still need the ORDER BY clause.
David Gugick
Quest Software
www.quest.com|||You have a ton of basic mistakes and need to get an intro book on SQL.
Columns are not fields. An auto-increment can never be a relational
key by definition because it is not an attribute of the entities
modeled in the table. Tables by definition have no ordering.
You are confusing an implementation with SQL and proper data modeling.
If you want to be sure that you get your output in order, you need an
ORDER BY clause.
Years ago, SQL Server programmers got screwed up becuase they wrote
code that assumed a GROUP BY clause would be done with a sort under the
covers, so they did not write the ORDER clause. It blew up a ton of
programs on the next release.|||Wow...guess I hit a nerve.
I'm sorry, I should have picked my words a little more carefully.
I figured you would get the jist of it....
Why would auto-increment fields not be an attribute of the entities in the
table?
Isn't that one of the easiest ways to get a unique identifier for that set
of data, making it a key attribute?
"can never be a relational key"...I've never heard that argument.|||There are different schools of thought on using auto-increment fields as
keys. Personally, I tend to agree with Celko on this one. Auto-increment
fields have nothing to do with the data in the table, they are an artificial
way of creating uniqueness. A more meaningful key would suit my tastes.
Using meaningful keys allows you to
1. Reuse the column name and data type in all tables, which makes joins more
intuitive.
Job.deptid = department.deptid
is easier and more intuitive than
job.Department = department.id
2. Use a key value that makes some sort of sense when you look at it.
3. Avoid redundancy in your table and insure normalization.
Often when we have an auto-incremeneting key, there is another column or
columns that also uniquely identify the row.
However, there are many in the field (with as much experience as myself or
Celko) that consider auto-increment keys a requirement in their tables.
As for the terminology, do a search on celko and punch cards to see why he
makes such an issue of it. As long as everone knows what you mean by
"field" and "record" (and we do) I wouldn't lose any sleep over it.
"INeedADip" <INeedADip@.gmail.com> wrote in message
news:eF8AOUEIGHA.2704@.TK2MSFTNGP15.phx.gbl...
> Wow...guess I hit a nerve.
> I'm sorry, I should have picked my words a little more carefully.
> I figured you would get the jist of it....
> Why would auto-increment fields not be an attribute of the entities in the
> table?
> Isn't that one of the easiest ways to get a unique identifier for that set
> of data, making it a key attribute?
> "can never be a relational key"...I've never heard that argument.
>|||So you guys are just refering to the name of the column?
I do have incrementing fields in 99% of my tables like AccountID, QueueID,
CampaignID, ect...
If that is what you guys are talking about, I agree....anyways...
Thanks for educating me on the Order By, I was under the impression that if
I used an index on the field, I could save some time by not using the Order
By. And just to note...I'm wasn't asking this question as a general rule,
but in this one case (this specific table) I was trying to save some time
because the table is so large and I will only be getting chunks in ascending
order EVERY TIME...but if it is not guaranteed, then I will add the "Order
By".
- Thanks|||Hi Jim,
I've got to take the bite and put the surrogate key side forward.
If a natural key exists on the table (that isn't in reality a generated
number in itself - for instance deptid would indicate its auto-generated
some how), most keys in the natural world are actually a surrogate of some
form or another.
There are problems duplicating the natural key everywhere, especially if its
a composite key; there is no difference in joining whether you use a
surrogate key or a natural one to join.
INeedADip - do a search on 'surrogate key' and 'identity' for some really
good and extensive discussions on the benefits of the surrogate key
approach.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:uNTKWeEIGHA.376@.TK2MSFTNGP12.phx.gbl...
> There are different schools of thought on using auto-increment fields as
> keys. Personally, I tend to agree with Celko on this one. Auto-increment
> fields have nothing to do with the data in the table, they are an
> artificial
> way of creating uniqueness. A more meaningful key would suit my tastes.
> Using meaningful keys allows you to
> 1. Reuse the column name and data type in all tables, which makes joins
> more
> intuitive.
> Job.deptid = department.deptid
> is easier and more intuitive than
> job.Department = department.id
> 2. Use a key value that makes some sort of sense when you look at it.
> 3. Avoid redundancy in your table and insure normalization.
> Often when we have an auto-incremeneting key, there is another column
> or
> columns that also uniquely identify the row.
> However, there are many in the field (with as much experience as myself or
> Celko) that consider auto-increment keys a requirement in their tables.
> As for the terminology, do a search on celko and punch cards to see why he
> makes such an issue of it. As long as everone knows what you mean by
> "field" and "record" (and we do) I wouldn't lose any sleep over it.
>
> "INeedADip" <INeedADip@.gmail.com> wrote in message
> news:eF8AOUEIGHA.2704@.TK2MSFTNGP15.phx.gbl...
>|||Thanks Tony.
I believe there was a fairly in depth discussion about this last w,
although I purge my postings frequently and can't locate it. If I recall
correctly, you were one of the folks involved in the discussion. Would you
mind posting a link to it?
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eyo318FIGHA.2912@.tk2msftngp13.phx.gbl...
> Hi Jim,
> I've got to take the bite and put the surrogate key side forward.
> If a natural key exists on the table (that isn't in reality a generated
> number in itself - for instance deptid would indicate its auto-generated
> some how), most keys in the natural world are actually a surrogate of some
> form or another.
> There are problems duplicating the natural key everywhere, especially if
its
> a composite key; there is no difference in joining whether you use a
> surrogate key or a natural one to join.
> INeedADip - do a search on 'surrogate key' and 'identity' for some really
> good and extensive discussions on the benefits of the surrogate key
> approach.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:uNTKWeEIGHA.376@.TK2MSFTNGP12.phx.gbl...
Auto-increment
or
he
>|||I think this is one of them...
6d2a8ccf940fe2" target="_blank">http://groups.google.co.uk/group/co...
6d2a8ccf940fe2
but there are many, search on 'surrogate key' rogerson --celko--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:eIiy5BGIGHA.2212@.TK2MSFTNGP15.phx.gbl...
> Thanks Tony.
> I believe there was a fairly in depth discussion about this last w,
> although I purge my postings frequently and can't locate it. If I recall
> correctly, you were one of the folks involved in the discussion. Would
> you
> mind posting a link to it?
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:eyo318FIGHA.2912@.tk2msftngp13.phx.gbl...
> its
> Auto-increment
> or
> he
>

Order by clause

hello everybody. i have a table named employee. where there is just a single column named id which is of type varchar. this is because i just wanted to play with order by clause. :D

in the table, i have CCA, cbC, cBC, CbC, CBC. when i queried with select * from employee order by id; the result is as followed-
CBC
cBC
cbC
CbC
CCA

how order by clause worked here, can anybody explain? thanks in advance...for the purpose of the ORDER BY, the collation you are using means that CBC, cBC, cbC, and CbC are all equivalent

CCA, of course, would come after all of them|||some databases treat upper & lower case the same, some dont, some can do either
some front ends likewise

so read the documentation with your db and your front end and see if you can select which order you want.

for example if you are using microsoft access as your front end 'option compare' may be worth investigating

MySQL has a mechanism at the database level to handle this sort of issue, and Im sure that will be the same for most other serious servers|||But if i write select * from employee order by id desc then the result is:
CCA
CBC
cBC
cbC
CbC

i don't have any idea how order by is working here...|||with DESC, CCA comes first, then all the rows with CBC equivalents|||if all rows with CBC are equivalent then why CbC comes last?? didn't get your point. plz explain.|||Yeah, that is a good point...
if SELECT * FROM employee ORDER BY id; gives the result
CBC
cBC
cbC
CbC
CCA

then SELECT * FROM employee ORDER BY id DESC; should provide-
CCA
CbC
cbC
cBC
CBC

why instead of it, the result is like this-
CCA
CBC
cBC
cbC
CbC?

or is it like the order of 4 CBCs doesn't really matter?:shocked:|||you know, it's beginning to look as if the order of the CBCs doesn't really matter because they're all equivalent

:)|||The sort order in databases is related with the Collation of that database.
For example Latin1_General_CI_AS is a Case Insensitive Accent Sensitive collation.

For a list of collations in MS SQL Server, you can use fn_helpcollations() function.
You can read the article http://www.kodyaz.com/content/fnhelpcollations.aspx on fn_helpcollations()

Eralper
http://www.kodyaz.com|||If the collation is case-insensitive, then to your database CBC and cbc appear to be the same thing... It doesn't matter which order they appear because for the purposes of comparision they are equal.

-PatP

order by bearing in mind seconds

hi,
How could I order by datetime type including the seconds?
Now I am using order by <field> and it' getting the values of this way:
2005-08-04 03:03:42.000
2005-08-04 04:00:33.000
2005-08-04 07:31:20.000
Instead of:
2005-08-04 07:31:20.000
2005-08-04 04:00:33.000
2005-08-04 03:03:42.000
Best wishes,Hi Enric,
I'm not sure what you think is wrong with the sorting that you get. It seems
perfectly valid.
Sorting by datetime includes ALL datetime elements (even the milisecconds).
If you want to sort ONLY by seconds, ingnoring the other parts, use:
order by datepart(second, dt)
BG, SQL Server MVP
www.SolidQualityLearning.com
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:F722D128-297D-4DD9-A8DC-05F34F714B87@.microsoft.com...
> hi,
> How could I order by datetime type including the seconds?
> Now I am using order by <field> and it' getting the values of this way:
> 2005-08-04 03:03:42.000
> 2005-08-04 04:00:33.000
> 2005-08-04 07:31:20.000
> Instead of:
> 2005-08-04 07:31:20.000
> 2005-08-04 04:00:33.000
> 2005-08-04 03:03:42.000
> Best wishes,
>
>
>
>|||I don't understand the question. The first example IS ordered by time
including the seconds. Ordering by a DATETIME column always takes
account of the whole date and time value, unless you perform some other
manipulation of the value.
David Portas
SQL Server MVP
--|||In order to accomplished you goad you presented you should just change the
sort order for that ?!
order by <col> DESC
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Enric" wrote:

> hi,
> How could I order by datetime type including the seconds?
> Now I am using order by <field> and it' getting the values of this way:
> 2005-08-04 03:03:42.000
> 2005-08-04 04:00:33.000
> 2005-08-04 07:31:20.000
> Instead of:
> 2005-08-04 07:31:20.000
> 2005-08-04 04:00:33.000
> 2005-08-04 03:03:42.000
> Best wishes,
>
>
>
>|||Thanks a lot to all,
"Jens Sü?meyer" wrote:
> In order to accomplished you goad you presented you should just change the
> sort order for that ?!
> order by <col> DESC
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Enric" wrote:
>

Oralce Distination

Hello Everyone,

I’m doing a type 2 SCD, Flat File as my source and oracle table as my destination but there is a problem in SCD component. I'm getting error.

is SSIS accessible to oracle as destination if yes please do inform me how do i resolve this issue.

Thank you

What is the error message? Please post that as without it we cannot help you.

An Oracle destination using OLE drivers will work just fine -- for the most part.

|||

The Error Message

TITLE: Microsoft Visual Studio

Error at Data Flow Task [OLE DB Command 1 [2007]]: An OLE DB error has occurred. Error code: 0x80040E51.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".

Error at Data Flow Task [OLE DB Command 1 [2007]]: Unable to retrieve destination column descriptions from the parameters of the SQL command.

Warning at {CF5DCB64-279E-45A4-A9A8-FF2FBB130980} [Insert Destination [1972]]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

ADDITIONAL INFORMATION:

Errors were encountered while generating the wizard results:
Error at Data Flow Task [OLE DB Command [1996]]: An OLE DB error has occurred. Error code: 0x80040E51.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".

Error at Data Flow Task [OLE DB Command [1996]]: Unable to retrieve destination column descriptions from the parameters of the SQL command.

Error at Data Flow Task [OLE DB Command 1 [2007]]: An OLE DB error has occurred. Error code: 0x80040E51.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".

Error at Data Flow Task [OLE DB Command 1 [2007]]: Unable to retrieve destination column descriptions from the parameters of the SQL command.

i had try to resolve the problem thru adding the derive parameter information.

but still i'm gettting an data conversation problem.

my source is a flat file and source column datatype is string but when i added the parameters its taking as unicode datatype can you help me in this.

thank you

Oracle query is SLOW, why ?

Hi,
Have the following sql analyzer query which takes 55 seconds to return
data.
SELECT i.TRANSACTION_DATE,
i.TYPE,
i.PART_ID,
i.WAREHOUSE_ID,
i.TRANSACTION_ID,
i.WORKORDER_BASE_ID,
i.QTY,
i.CLASS
FROM VMFG..SYSADM.INVENTORY_TRANS i
WHERE i.TRANSACTION_ID = 526123 and i.CLASS = 'R' and i.TYPE = 'I'
order by i.TRANSACTION_ID
Remove the and's and the response goes down to 1 second.
SELECT i.TRANSACTION_DATE,
i.TYPE,
i.PART_ID,
i.WAREHOUSE_ID,
i.TRANSACTION_ID,
i.WORKORDER_BASE_ID,
i.QTY,
i.CLASS
FROM VMFG..SYSADM.INVENTORY_TRANS i
WHERE i.TRANSACTION_ID = 526123
order by i.TRANSACTION_ID
The question is why ? If you look at the data in the single row returned...
2004-10-22 00:00:00.000 I 76307-03005-061 QA ACCEPTED 526123
17408 1.0000 R
the record clearly contains the correct data. Any insight on this GREATLY
appreciated because we have a reporting services query based on a date range
which also has the same poor response time. If you put the same query into an
Oracle sql Plus query the data returns instantly.
Thanks, Steve.How do you connect...?
In the dts-newsgroup heard some discussions to use text file when
transporting data from oracle to sql - so perhaps you need to transport
these using some kind of bulk export and query on top of an sql table
instead.
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:A948FA70-BB51-401E-B7B5-442340F37F16@.microsoft.com...
> Hi,
> Have the following sql analyzer query which takes 55 seconds to return
> data.
> SELECT i.TRANSACTION_DATE,
> i.TYPE,
> i.PART_ID,
> i.WAREHOUSE_ID,
> i.TRANSACTION_ID,
> i.WORKORDER_BASE_ID,
> i.QTY,
> i.CLASS
> FROM VMFG..SYSADM.INVENTORY_TRANS i
> WHERE i.TRANSACTION_ID = 526123 and i.CLASS = 'R' and i.TYPE = 'I'
> order by i.TRANSACTION_ID
> Remove the and's and the response goes down to 1 second.
> SELECT i.TRANSACTION_DATE,
> i.TYPE,
> i.PART_ID,
> i.WAREHOUSE_ID,
> i.TRANSACTION_ID,
> i.WORKORDER_BASE_ID,
> i.QTY,
> i.CLASS
> FROM VMFG..SYSADM.INVENTORY_TRANS i
> WHERE i.TRANSACTION_ID = 526123
> order by i.TRANSACTION_ID
> The question is why ? If you look at the data in the single row
returned...
> 2004-10-22 00:00:00.000 I 76307-03005-061 QA ACCEPTED 526123
> 17408 1.0000 R
> the record clearly contains the correct data. Any insight on this GREATLY
> appreciated because we have a reporting services query based on a date
range
> which also has the same poor response time. If you put the same query into
an
> Oracle sql Plus query the data returns instantly.
> Thanks, Steve.
>|||You mention query analyzer and Oracle sql plus. OK, that makes me think you
are using linked servers. Try the query plan with the query analyzer and I
bet you find out that it is pulling all the data locally and then applying
the where clause. You have several options. One is to use Openquery instead
of the 4 part syntax. The other is to not use linked database. I only use
linked databases as a last resort. I suggest using a shared datasource in RS
to Oracle instead. For straight reporting I never use linked databases. It
complicates matters and buys you very little. What I do is have a shared
datasource that has a readonly user credential stored by RS. This takes
advantage of connection pooling and will be either much faster or much
easier or both than using linked databases.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:A948FA70-BB51-401E-B7B5-442340F37F16@.microsoft.com...
> Hi,
> Have the following sql analyzer query which takes 55 seconds to return
> data.
> SELECT i.TRANSACTION_DATE,
> i.TYPE,
> i.PART_ID,
> i.WAREHOUSE_ID,
> i.TRANSACTION_ID,
> i.WORKORDER_BASE_ID,
> i.QTY,
> i.CLASS
> FROM VMFG..SYSADM.INVENTORY_TRANS i
> WHERE i.TRANSACTION_ID = 526123 and i.CLASS = 'R' and i.TYPE = 'I'
> order by i.TRANSACTION_ID
> Remove the and's and the response goes down to 1 second.
> SELECT i.TRANSACTION_DATE,
> i.TYPE,
> i.PART_ID,
> i.WAREHOUSE_ID,
> i.TRANSACTION_ID,
> i.WORKORDER_BASE_ID,
> i.QTY,
> i.CLASS
> FROM VMFG..SYSADM.INVENTORY_TRANS i
> WHERE i.TRANSACTION_ID = 526123
> order by i.TRANSACTION_ID
> The question is why ? If you look at the data in the single row
returned...
> 2004-10-22 00:00:00.000 I 76307-03005-061 QA ACCEPTED 526123
> 17408 1.0000 R
> the record clearly contains the correct data. Any insight on this GREATLY
> appreciated because we have a reporting services query based on a date
range
> which also has the same poor response time. If you put the same query into
an
> Oracle sql Plus query the data returns instantly.
> Thanks, Steve.
>|||Through a linked server
"Michael Vardinghus" wrote:
> How do you connect...?
> In the dts-newsgroup heard some discussions to use text file when
> transporting data from oracle to sql - so perhaps you need to transport
> these using some kind of bulk export and query on top of an sql table
> instead.
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:A948FA70-BB51-401E-B7B5-442340F37F16@.microsoft.com...
> > Hi,
> > Have the following sql analyzer query which takes 55 seconds to return
> > data.
> > SELECT i.TRANSACTION_DATE,
> > i.TYPE,
> > i.PART_ID,
> > i.WAREHOUSE_ID,
> > i.TRANSACTION_ID,
> > i.WORKORDER_BASE_ID,
> > i.QTY,
> > i.CLASS
> > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > WHERE i.TRANSACTION_ID = 526123 and i.CLASS = 'R' and i.TYPE = 'I'
> > order by i.TRANSACTION_ID
> >
> > Remove the and's and the response goes down to 1 second.
> > SELECT i.TRANSACTION_DATE,
> > i.TYPE,
> > i.PART_ID,
> > i.WAREHOUSE_ID,
> > i.TRANSACTION_ID,
> > i.WORKORDER_BASE_ID,
> > i.QTY,
> > i.CLASS
> > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > WHERE i.TRANSACTION_ID = 526123
> > order by i.TRANSACTION_ID
> >
> > The question is why ? If you look at the data in the single row
> returned...
> > 2004-10-22 00:00:00.000 I 76307-03005-061 QA ACCEPTED 526123
> > 17408 1.0000 R
> > the record clearly contains the correct data. Any insight on this GREATLY
> > appreciated because we have a reporting services query based on a date
> range
> > which also has the same poor response time. If you put the same query into
> an
> > Oracle sql Plus query the data returns instantly.
> >
> > Thanks, Steve.
> >
> >
>
>|||Hi Bruce,
Right you are !! Will research creating a "shared data source".
Do not see a ton of info in help on this. Are you able to reference this
shared data source from your stored procedures just as you do the linked
server ?
Many thanks, Steve.
"Bruce L-C [MVP]" wrote:
> You mention query analyzer and Oracle sql plus. OK, that makes me think you
> are using linked servers. Try the query plan with the query analyzer and I
> bet you find out that it is pulling all the data locally and then applying
> the where clause. You have several options. One is to use Openquery instead
> of the 4 part syntax. The other is to not use linked database. I only use
> linked databases as a last resort. I suggest using a shared datasource in RS
> to Oracle instead. For straight reporting I never use linked databases. It
> complicates matters and buys you very little. What I do is have a shared
> datasource that has a readonly user credential stored by RS. This takes
> advantage of connection pooling and will be either much faster or much
> easier or both than using linked databases.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:A948FA70-BB51-401E-B7B5-442340F37F16@.microsoft.com...
> > Hi,
> > Have the following sql analyzer query which takes 55 seconds to return
> > data.
> > SELECT i.TRANSACTION_DATE,
> > i.TYPE,
> > i.PART_ID,
> > i.WAREHOUSE_ID,
> > i.TRANSACTION_ID,
> > i.WORKORDER_BASE_ID,
> > i.QTY,
> > i.CLASS
> > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > WHERE i.TRANSACTION_ID = 526123 and i.CLASS = 'R' and i.TYPE = 'I'
> > order by i.TRANSACTION_ID
> >
> > Remove the and's and the response goes down to 1 second.
> > SELECT i.TRANSACTION_DATE,
> > i.TYPE,
> > i.PART_ID,
> > i.WAREHOUSE_ID,
> > i.TRANSACTION_ID,
> > i.WORKORDER_BASE_ID,
> > i.QTY,
> > i.CLASS
> > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > WHERE i.TRANSACTION_ID = 526123
> > order by i.TRANSACTION_ID
> >
> > The question is why ? If you look at the data in the single row
> returned...
> > 2004-10-22 00:00:00.000 I 76307-03005-061 QA ACCEPTED 526123
> > 17408 1.0000 R
> > the record clearly contains the correct data. Any insight on this GREATLY
> > appreciated because we have a reporting services query based on a date
> range
> > which also has the same poor response time. If you put the same query into
> an
> > Oracle sql Plus query the data returns instantly.
> >
> > Thanks, Steve.
> >
> >
>
>|||Hi Bruce,
Many thanks for your reply ! Absolutely correct on all accounts
(linked server, plan). Am now researching the "shared datasource" option. Do
you still reference it from your stored procedures ? That probably does not
change.
Any tech article tips or links on this greatly appreciated. Steve.
"Bruce L-C [MVP]" wrote:
> You mention query analyzer and Oracle sql plus. OK, that makes me think you
> are using linked servers. Try the query plan with the query analyzer and I
> bet you find out that it is pulling all the data locally and then applying
> the where clause. You have several options. One is to use Openquery instead
> of the 4 part syntax. The other is to not use linked database. I only use
> linked databases as a last resort. I suggest using a shared datasource in RS
> to Oracle instead. For straight reporting I never use linked databases. It
> complicates matters and buys you very little. What I do is have a shared
> datasource that has a readonly user credential stored by RS. This takes
> advantage of connection pooling and will be either much faster or much
> easier or both than using linked databases.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:A948FA70-BB51-401E-B7B5-442340F37F16@.microsoft.com...
> > Hi,
> > Have the following sql analyzer query which takes 55 seconds to return
> > data.
> > SELECT i.TRANSACTION_DATE,
> > i.TYPE,
> > i.PART_ID,
> > i.WAREHOUSE_ID,
> > i.TRANSACTION_ID,
> > i.WORKORDER_BASE_ID,
> > i.QTY,
> > i.CLASS
> > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > WHERE i.TRANSACTION_ID = 526123 and i.CLASS = 'R' and i.TYPE = 'I'
> > order by i.TRANSACTION_ID
> >
> > Remove the and's and the response goes down to 1 second.
> > SELECT i.TRANSACTION_DATE,
> > i.TYPE,
> > i.PART_ID,
> > i.WAREHOUSE_ID,
> > i.TRANSACTION_ID,
> > i.WORKORDER_BASE_ID,
> > i.QTY,
> > i.CLASS
> > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > WHERE i.TRANSACTION_ID = 526123
> > order by i.TRANSACTION_ID
> >
> > The question is why ? If you look at the data in the single row
> returned...
> > 2004-10-22 00:00:00.000 I 76307-03005-061 QA ACCEPTED 526123
> > 17408 1.0000 R
> > the record clearly contains the correct data. Any insight on this GREATLY
> > appreciated because we have a reporting services query based on a date
> range
> > which also has the same poor response time. If you put the same query into
> an
> > Oracle sql Plus query the data returns instantly.
> >
> > Thanks, Steve.
> >
> >
>
>|||Shared datasources are a RS thing, not a SQL Server thing. If what you are
doing is creating a query for the dataset in RS then you can use a shared
data source that the report is based on that goes to Oracle instead of to
SQL Server. If you have to use a stored procedure that resides in SQL Server
then you have to use OpenQuery AND you have to assemble the string to use
with it since you can not use a parameter with openquery. So, if you can get
away with either not using the stored procedure in SQL Server OR if you can
put the stored procedure in Oracle instead then that would be better. So
your options are:
1. Put the query in Report Services dataset and use a data source that goes
directly against Oracle
2. Use the SQL Server stored procedure with OpenQuery assembling the query
string (which gets to be a real pain with single quotes)
3. Use Oracle Stored predures.
Here is an example of some OpenQuery (I happen to be hip deep in this right
now).
select @.FROM = ''' + convert(varchar(30),@.UPDATEFROM,9) + '''
select @.TO = ''' + convert(varchar(30),@.UPDATETO,9) + '''
select @.SQL = 'insert collect_values select * from openquery(linktest,''' +
'SELECT * from collect_values where time_tag > ' + @.FROM + ' and time_tag <=' + @.TO + ''')'
execute (@.SQL)
Notice the wonderful messing with single quotes.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:DFDD294D-F8F7-4445-817E-C59C012FB32B@.microsoft.com...
> Hi Bruce,
> Many thanks for your reply ! Absolutely correct on all
accounts
> (linked server, plan). Am now researching the "shared datasource" option.
Do
> you still reference it from your stored procedures ? That probably does
not
> change.
> Any tech article tips or links on this greatly appreciated. Steve.
> "Bruce L-C [MVP]" wrote:
> > You mention query analyzer and Oracle sql plus. OK, that makes me think
you
> > are using linked servers. Try the query plan with the query analyzer and
I
> > bet you find out that it is pulling all the data locally and then
applying
> > the where clause. You have several options. One is to use Openquery
instead
> > of the 4 part syntax. The other is to not use linked database. I only
use
> > linked databases as a last resort. I suggest using a shared datasource
in RS
> > to Oracle instead. For straight reporting I never use linked databases.
It
> > complicates matters and buys you very little. What I do is have a shared
> > datasource that has a readonly user credential stored by RS. This takes
> > advantage of connection pooling and will be either much faster or much
> > easier or both than using linked databases.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> > news:A948FA70-BB51-401E-B7B5-442340F37F16@.microsoft.com...
> > > Hi,
> > > Have the following sql analyzer query which takes 55 seconds to
return
> > > data.
> > > SELECT i.TRANSACTION_DATE,
> > > i.TYPE,
> > > i.PART_ID,
> > > i.WAREHOUSE_ID,
> > > i.TRANSACTION_ID,
> > > i.WORKORDER_BASE_ID,
> > > i.QTY,
> > > i.CLASS
> > > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > > WHERE i.TRANSACTION_ID = 526123 and i.CLASS = 'R' and i.TYPE = 'I'
> > > order by i.TRANSACTION_ID
> > >
> > > Remove the and's and the response goes down to 1 second.
> > > SELECT i.TRANSACTION_DATE,
> > > i.TYPE,
> > > i.PART_ID,
> > > i.WAREHOUSE_ID,
> > > i.TRANSACTION_ID,
> > > i.WORKORDER_BASE_ID,
> > > i.QTY,
> > > i.CLASS
> > > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > > WHERE i.TRANSACTION_ID = 526123
> > > order by i.TRANSACTION_ID
> > >
> > > The question is why ? If you look at the data in the single row
> > returned...
> > > 2004-10-22 00:00:00.000 I 76307-03005-061 QA ACCEPTED
526123
> > > 17408 1.0000 R
> > > the record clearly contains the correct data. Any insight on this
GREATLY
> > > appreciated because we have a reporting services query based on a date
> > range
> > > which also has the same poor response time. If you put the same query
into
> > an
> > > Oracle sql Plus query the data returns instantly.
> > >
> > > Thanks, Steve.
> > >
> > >
> >
> >
> >|||Would you use this approach if you were to transport data every night from
oracle to sql or
would you use some kind of bulk export instead ?
/Michael V.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:efHSNuL2EHA.936@.TK2MSFTNGP12.phx.gbl...
> Shared datasources are a RS thing, not a SQL Server thing. If what you are
> doing is creating a query for the dataset in RS then you can use a shared
> data source that the report is based on that goes to Oracle instead of to
> SQL Server. If you have to use a stored procedure that resides in SQL
Server
> then you have to use OpenQuery AND you have to assemble the string to use
> with it since you can not use a parameter with openquery. So, if you can
get
> away with either not using the stored procedure in SQL Server OR if you
can
> put the stored procedure in Oracle instead then that would be better. So
> your options are:
> 1. Put the query in Report Services dataset and use a data source that
goes
> directly against Oracle
> 2. Use the SQL Server stored procedure with OpenQuery assembling the query
> string (which gets to be a real pain with single quotes)
> 3. Use Oracle Stored predures.
> Here is an example of some OpenQuery (I happen to be hip deep in this
right
> now).
> select @.FROM = ''' + convert(varchar(30),@.UPDATEFROM,9) + '''
> select @.TO = ''' + convert(varchar(30),@.UPDATETO,9) + '''
> select @.SQL = 'insert collect_values select * from openquery(linktest,'''
+
> 'SELECT * from collect_values where time_tag > ' + @.FROM + ' and time_tag
<=> ' + @.TO + ''')'
> execute (@.SQL)
> Notice the wonderful messing with single quotes.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:DFDD294D-F8F7-4445-817E-C59C012FB32B@.microsoft.com...
> > Hi Bruce,
> > Many thanks for your reply ! Absolutely correct on all
> accounts
> > (linked server, plan). Am now researching the "shared datasource"
option.
> Do
> > you still reference it from your stored procedures ? That probably does
> not
> > change.
> > Any tech article tips or links on this greatly appreciated. Steve.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > You mention query analyzer and Oracle sql plus. OK, that makes me
think
> you
> > > are using linked servers. Try the query plan with the query analyzer
and
> I
> > > bet you find out that it is pulling all the data locally and then
> applying
> > > the where clause. You have several options. One is to use Openquery
> instead
> > > of the 4 part syntax. The other is to not use linked database. I only
> use
> > > linked databases as a last resort. I suggest using a shared datasource
> in RS
> > > to Oracle instead. For straight reporting I never use linked
databases.
> It
> > > complicates matters and buys you very little. What I do is have a
shared
> > > datasource that has a readonly user credential stored by RS. This
takes
> > > advantage of connection pooling and will be either much faster or much
> > > easier or both than using linked databases.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> > > news:A948FA70-BB51-401E-B7B5-442340F37F16@.microsoft.com...
> > > > Hi,
> > > > Have the following sql analyzer query which takes 55 seconds to
> return
> > > > data.
> > > > SELECT i.TRANSACTION_DATE,
> > > > i.TYPE,
> > > > i.PART_ID,
> > > > i.WAREHOUSE_ID,
> > > > i.TRANSACTION_ID,
> > > > i.WORKORDER_BASE_ID,
> > > > i.QTY,
> > > > i.CLASS
> > > > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > > > WHERE i.TRANSACTION_ID = 526123 and i.CLASS = 'R' and i.TYPE = 'I'
> > > > order by i.TRANSACTION_ID
> > > >
> > > > Remove the and's and the response goes down to 1 second.
> > > > SELECT i.TRANSACTION_DATE,
> > > > i.TYPE,
> > > > i.PART_ID,
> > > > i.WAREHOUSE_ID,
> > > > i.TRANSACTION_ID,
> > > > i.WORKORDER_BASE_ID,
> > > > i.QTY,
> > > > i.CLASS
> > > > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > > > WHERE i.TRANSACTION_ID = 526123
> > > > order by i.TRANSACTION_ID
> > > >
> > > > The question is why ? If you look at the data in the single row
> > > returned...
> > > > 2004-10-22 00:00:00.000 I 76307-03005-061 QA ACCEPTED
> 526123
> > > > 17408 1.0000 R
> > > > the record clearly contains the correct data. Any insight on this
> GREATLY
> > > > appreciated because we have a reporting services query based on a
date
> > > range
> > > > which also has the same poor response time. If you put the same
query
> into
> > > an
> > > > Oracle sql Plus query the data returns instantly.
> > > >
> > > > Thanks, Steve.
> > > >
> > > >
> > >
> > >
> > >
>|||Hmmm, this has nothing to do with Reporting Services but that doesn't mean I
don't have an opinion <g>.
It depends on what you are doing and how much data you have plus how complex
is the extraction you are doing. You should look at DTS if you have
transformations you are doing. If all you are doing is pulling and loading
data again it depends on the quantity. It is very easy to do a quick test. I
just happen to be working on a datamart that I am keeping in sync every 5
minutes. There are some tables that I just do once a night and they are more
like bulk load. What I do is I have a database in SQL Server that is for
syncing. It has just the tables with no indexes, pk or anything, just the
columns. I use the openquery technique and insert the data into the table (I
add other error checking, for instance if the table already has data in it
then it means the process failed and I don't do anything). This comes over
very fast. Then I move the data from that table to the target table in the
other database (which is on the same server). I would do a test, you might
be surprised how fast this technique is.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael Vardinghus" <michaelvardinghus@.hotmail.com> wrote in message
news:e7T2QNR2EHA.304@.TK2MSFTNGP11.phx.gbl...
> Would you use this approach if you were to transport data every night from
> oracle to sql or
> would you use some kind of bulk export instead ?
> /Michael V.
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:efHSNuL2EHA.936@.TK2MSFTNGP12.phx.gbl...
> > Shared datasources are a RS thing, not a SQL Server thing. If what you
are
> > doing is creating a query for the dataset in RS then you can use a
shared
> > data source that the report is based on that goes to Oracle instead of
to
> > SQL Server. If you have to use a stored procedure that resides in SQL
> Server
> > then you have to use OpenQuery AND you have to assemble the string to
use
> > with it since you can not use a parameter with openquery. So, if you can
> get
> > away with either not using the stored procedure in SQL Server OR if you
> can
> > put the stored procedure in Oracle instead then that would be better. So
> > your options are:
> > 1. Put the query in Report Services dataset and use a data source that
> goes
> > directly against Oracle
> > 2. Use the SQL Server stored procedure with OpenQuery assembling the
query
> > string (which gets to be a real pain with single quotes)
> > 3. Use Oracle Stored predures.
> >
> > Here is an example of some OpenQuery (I happen to be hip deep in this
> right
> > now).
> >
> > select @.FROM = ''' + convert(varchar(30),@.UPDATEFROM,9) + '''
> > select @.TO = ''' + convert(varchar(30),@.UPDATETO,9) + '''
> > select @.SQL = 'insert collect_values select * from
openquery(linktest,'''
> +
> > 'SELECT * from collect_values where time_tag > ' + @.FROM + ' and
time_tag
> <=> > ' + @.TO + ''')'
> > execute (@.SQL)
> >
> > Notice the wonderful messing with single quotes.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> > news:DFDD294D-F8F7-4445-817E-C59C012FB32B@.microsoft.com...
> > > Hi Bruce,
> > > Many thanks for your reply ! Absolutely correct on all
> > accounts
> > > (linked server, plan). Am now researching the "shared datasource"
> option.
> > Do
> > > you still reference it from your stored procedures ? That probably
does
> > not
> > > change.
> > > Any tech article tips or links on this greatly appreciated. Steve.
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > You mention query analyzer and Oracle sql plus. OK, that makes me
> think
> > you
> > > > are using linked servers. Try the query plan with the query analyzer
> and
> > I
> > > > bet you find out that it is pulling all the data locally and then
> > applying
> > > > the where clause. You have several options. One is to use Openquery
> > instead
> > > > of the 4 part syntax. The other is to not use linked database. I
only
> > use
> > > > linked databases as a last resort. I suggest using a shared
datasource
> > in RS
> > > > to Oracle instead. For straight reporting I never use linked
> databases.
> > It
> > > > complicates matters and buys you very little. What I do is have a
> shared
> > > > datasource that has a readonly user credential stored by RS. This
> takes
> > > > advantage of connection pooling and will be either much faster or
much
> > > > easier or both than using linked databases.
> > > >
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> > > > news:A948FA70-BB51-401E-B7B5-442340F37F16@.microsoft.com...
> > > > > Hi,
> > > > > Have the following sql analyzer query which takes 55 seconds to
> > return
> > > > > data.
> > > > > SELECT i.TRANSACTION_DATE,
> > > > > i.TYPE,
> > > > > i.PART_ID,
> > > > > i.WAREHOUSE_ID,
> > > > > i.TRANSACTION_ID,
> > > > > i.WORKORDER_BASE_ID,
> > > > > i.QTY,
> > > > > i.CLASS
> > > > > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > > > > WHERE i.TRANSACTION_ID = 526123 and i.CLASS = 'R' and i.TYPE = 'I'
> > > > > order by i.TRANSACTION_ID
> > > > >
> > > > > Remove the and's and the response goes down to 1 second.
> > > > > SELECT i.TRANSACTION_DATE,
> > > > > i.TYPE,
> > > > > i.PART_ID,
> > > > > i.WAREHOUSE_ID,
> > > > > i.TRANSACTION_ID,
> > > > > i.WORKORDER_BASE_ID,
> > > > > i.QTY,
> > > > > i.CLASS
> > > > > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > > > > WHERE i.TRANSACTION_ID = 526123
> > > > > order by i.TRANSACTION_ID
> > > > >
> > > > > The question is why ? If you look at the data in the single row
> > > > returned...
> > > > > 2004-10-22 00:00:00.000 I 76307-03005-061 QA ACCEPTED
> > 526123
> > > > > 17408 1.0000 R
> > > > > the record clearly contains the correct data. Any insight on this
> > GREATLY
> > > > > appreciated because we have a reporting services query based on a
> date
> > > > range
> > > > > which also has the same poor response time. If you put the same
> query
> > into
> > > > an
> > > > > Oracle sql Plus query the data returns instantly.
> > > > >
> > > > > Thanks, Steve.
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
>|||Hi Bruce,
Thanks for your reply !!! Mission accomplished. Really just a
matter of selectinng the oracle driver (from oracle) when configuring the
linked server. Performance is instant ! Details are in Metalink doc
Note:191368.1.
Thanks again. Steve.
"Bruce L-C [MVP]" wrote:
> Hmmm, this has nothing to do with Reporting Services but that doesn't mean I
> don't have an opinion <g>.
> It depends on what you are doing and how much data you have plus how complex
> is the extraction you are doing. You should look at DTS if you have
> transformations you are doing. If all you are doing is pulling and loading
> data again it depends on the quantity. It is very easy to do a quick test. I
> just happen to be working on a datamart that I am keeping in sync every 5
> minutes. There are some tables that I just do once a night and they are more
> like bulk load. What I do is I have a database in SQL Server that is for
> syncing. It has just the tables with no indexes, pk or anything, just the
> columns. I use the openquery technique and insert the data into the table (I
> add other error checking, for instance if the table already has data in it
> then it means the process failed and I don't do anything). This comes over
> very fast. Then I move the data from that table to the target table in the
> other database (which is on the same server). I would do a test, you might
> be surprised how fast this technique is.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Michael Vardinghus" <michaelvardinghus@.hotmail.com> wrote in message
> news:e7T2QNR2EHA.304@.TK2MSFTNGP11.phx.gbl...
> > Would you use this approach if you were to transport data every night from
> > oracle to sql or
> > would you use some kind of bulk export instead ?
> >
> > /Michael V.
> >
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > news:efHSNuL2EHA.936@.TK2MSFTNGP12.phx.gbl...
> > > Shared datasources are a RS thing, not a SQL Server thing. If what you
> are
> > > doing is creating a query for the dataset in RS then you can use a
> shared
> > > data source that the report is based on that goes to Oracle instead of
> to
> > > SQL Server. If you have to use a stored procedure that resides in SQL
> > Server
> > > then you have to use OpenQuery AND you have to assemble the string to
> use
> > > with it since you can not use a parameter with openquery. So, if you can
> > get
> > > away with either not using the stored procedure in SQL Server OR if you
> > can
> > > put the stored procedure in Oracle instead then that would be better. So
> > > your options are:
> > > 1. Put the query in Report Services dataset and use a data source that
> > goes
> > > directly against Oracle
> > > 2. Use the SQL Server stored procedure with OpenQuery assembling the
> query
> > > string (which gets to be a real pain with single quotes)
> > > 3. Use Oracle Stored predures.
> > >
> > > Here is an example of some OpenQuery (I happen to be hip deep in this
> > right
> > > now).
> > >
> > > select @.FROM = ''' + convert(varchar(30),@.UPDATEFROM,9) + '''
> > > select @.TO = ''' + convert(varchar(30),@.UPDATETO,9) + '''
> > > select @.SQL = 'insert collect_values select * from
> openquery(linktest,'''
> > +
> > > 'SELECT * from collect_values where time_tag > ' + @.FROM + ' and
> time_tag
> > <=> > > ' + @.TO + ''')'
> > > execute (@.SQL)
> > >
> > > Notice the wonderful messing with single quotes.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> > > news:DFDD294D-F8F7-4445-817E-C59C012FB32B@.microsoft.com...
> > > > Hi Bruce,
> > > > Many thanks for your reply ! Absolutely correct on all
> > > accounts
> > > > (linked server, plan). Am now researching the "shared datasource"
> > option.
> > > Do
> > > > you still reference it from your stored procedures ? That probably
> does
> > > not
> > > > change.
> > > > Any tech article tips or links on this greatly appreciated. Steve.
> > > >
> > > > "Bruce L-C [MVP]" wrote:
> > > >
> > > > > You mention query analyzer and Oracle sql plus. OK, that makes me
> > think
> > > you
> > > > > are using linked servers. Try the query plan with the query analyzer
> > and
> > > I
> > > > > bet you find out that it is pulling all the data locally and then
> > > applying
> > > > > the where clause. You have several options. One is to use Openquery
> > > instead
> > > > > of the 4 part syntax. The other is to not use linked database. I
> only
> > > use
> > > > > linked databases as a last resort. I suggest using a shared
> datasource
> > > in RS
> > > > > to Oracle instead. For straight reporting I never use linked
> > databases.
> > > It
> > > > > complicates matters and buys you very little. What I do is have a
> > shared
> > > > > datasource that has a readonly user credential stored by RS. This
> > takes
> > > > > advantage of connection pooling and will be either much faster or
> much
> > > > > easier or both than using linked databases.
> > > > >
> > > > >
> > > > > --
> > > > > Bruce Loehle-Conger
> > > > > MVP SQL Server Reporting Services
> > > > >
> > > > > "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> > > > > news:A948FA70-BB51-401E-B7B5-442340F37F16@.microsoft.com...
> > > > > > Hi,
> > > > > > Have the following sql analyzer query which takes 55 seconds to
> > > return
> > > > > > data.
> > > > > > SELECT i.TRANSACTION_DATE,
> > > > > > i.TYPE,
> > > > > > i.PART_ID,
> > > > > > i.WAREHOUSE_ID,
> > > > > > i.TRANSACTION_ID,
> > > > > > i.WORKORDER_BASE_ID,
> > > > > > i.QTY,
> > > > > > i.CLASS
> > > > > > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > > > > > WHERE i.TRANSACTION_ID = 526123 and i.CLASS = 'R' and i.TYPE = 'I'
> > > > > > order by i.TRANSACTION_ID
> > > > > >
> > > > > > Remove the and's and the response goes down to 1 second.
> > > > > > SELECT i.TRANSACTION_DATE,
> > > > > > i.TYPE,
> > > > > > i.PART_ID,
> > > > > > i.WAREHOUSE_ID,
> > > > > > i.TRANSACTION_ID,
> > > > > > i.WORKORDER_BASE_ID,
> > > > > > i.QTY,
> > > > > > i.CLASS
> > > > > > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > > > > > WHERE i.TRANSACTION_ID = 526123
> > > > > > order by i.TRANSACTION_ID
> > > > > >
> > > > > > The question is why ? If you look at the data in the single row
> > > > > returned...
> > > > > > 2004-10-22 00:00:00.000 I 76307-03005-061 QA ACCEPTED
> > > 526123
> > > > > > 17408 1.0000 R
> > > > > > the record clearly contains the correct data. Any insight on this
> > > GREATLY
> > > > > > appreciated because we have a reporting services query based on a
> > date
> > > > > range
> > > > > > which also has the same poor response time. If you put the same
> > query
> > > into
> > > > > an
> > > > > > Oracle sql Plus query the data returns instantly.
> > > > > >
> > > > > > Thanks, Steve.
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> >
> >
>
>|||You will still need to be watchful if you use the 4 part naming. The
decision on what you send to Oracle and what to process locally is done by
SQL Server. Looking at the query plan in query analyzer will always show you
when this has happened.
In particular, the where clause can all of a sudden cause a the processing
to occur locally.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:70A1E1CF-95A4-422B-9026-3837715525D0@.microsoft.com...
> Hi Bruce,
> Thanks for your reply !!! Mission accomplished. Really just
a
> matter of selectinng the oracle driver (from oracle) when configuring the
> linked server. Performance is instant ! Details are in Metalink doc
> Note:191368.1.
> Thanks again. Steve.
> "Bruce L-C [MVP]" wrote:
> > Hmmm, this has nothing to do with Reporting Services but that doesn't
mean I
> > don't have an opinion <g>.
> >
> > It depends on what you are doing and how much data you have plus how
complex
> > is the extraction you are doing. You should look at DTS if you have
> > transformations you are doing. If all you are doing is pulling and
loading
> > data again it depends on the quantity. It is very easy to do a quick
test. I
> > just happen to be working on a datamart that I am keeping in sync every
5
> > minutes. There are some tables that I just do once a night and they are
more
> > like bulk load. What I do is I have a database in SQL Server that is for
> > syncing. It has just the tables with no indexes, pk or anything, just
the
> > columns. I use the openquery technique and insert the data into the
table (I
> > add other error checking, for instance if the table already has data in
it
> > then it means the process failed and I don't do anything). This comes
over
> > very fast. Then I move the data from that table to the target table in
the
> > other database (which is on the same server). I would do a test, you
might
> > be surprised how fast this technique is.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Michael Vardinghus" <michaelvardinghus@.hotmail.com> wrote in message
> > news:e7T2QNR2EHA.304@.TK2MSFTNGP11.phx.gbl...
> > > Would you use this approach if you were to transport data every night
from
> > > oracle to sql or
> > > would you use some kind of bulk export instead ?
> > >
> > > /Michael V.
> > >
> > >
> > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > > news:efHSNuL2EHA.936@.TK2MSFTNGP12.phx.gbl...
> > > > Shared datasources are a RS thing, not a SQL Server thing. If what
you
> > are
> > > > doing is creating a query for the dataset in RS then you can use a
> > shared
> > > > data source that the report is based on that goes to Oracle instead
of
> > to
> > > > SQL Server. If you have to use a stored procedure that resides in
SQL
> > > Server
> > > > then you have to use OpenQuery AND you have to assemble the string
to
> > use
> > > > with it since you can not use a parameter with openquery. So, if you
can
> > > get
> > > > away with either not using the stored procedure in SQL Server OR if
you
> > > can
> > > > put the stored procedure in Oracle instead then that would be
better. So
> > > > your options are:
> > > > 1. Put the query in Report Services dataset and use a data source
that
> > > goes
> > > > directly against Oracle
> > > > 2. Use the SQL Server stored procedure with OpenQuery assembling the
> > query
> > > > string (which gets to be a real pain with single quotes)
> > > > 3. Use Oracle Stored predures.
> > > >
> > > > Here is an example of some OpenQuery (I happen to be hip deep in
this
> > > right
> > > > now).
> > > >
> > > > select @.FROM = ''' + convert(varchar(30),@.UPDATEFROM,9) + '''
> > > > select @.TO = ''' + convert(varchar(30),@.UPDATETO,9) + '''
> > > > select @.SQL = 'insert collect_values select * from
> > openquery(linktest,'''
> > > +
> > > > 'SELECT * from collect_values where time_tag > ' + @.FROM + ' and
> > time_tag
> > > <=> > > > ' + @.TO + ''')'
> > > > execute (@.SQL)
> > > >
> > > > Notice the wonderful messing with single quotes.
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> > > > news:DFDD294D-F8F7-4445-817E-C59C012FB32B@.microsoft.com...
> > > > > Hi Bruce,
> > > > > Many thanks for your reply ! Absolutely correct on
all
> > > > accounts
> > > > > (linked server, plan). Am now researching the "shared datasource"
> > > option.
> > > > Do
> > > > > you still reference it from your stored procedures ? That probably
> > does
> > > > not
> > > > > change.
> > > > > Any tech article tips or links on this greatly appreciated. Steve.
> > > > >
> > > > > "Bruce L-C [MVP]" wrote:
> > > > >
> > > > > > You mention query analyzer and Oracle sql plus. OK, that makes
me
> > > think
> > > > you
> > > > > > are using linked servers. Try the query plan with the query
analyzer
> > > and
> > > > I
> > > > > > bet you find out that it is pulling all the data locally and
then
> > > > applying
> > > > > > the where clause. You have several options. One is to use
Openquery
> > > > instead
> > > > > > of the 4 part syntax. The other is to not use linked database. I
> > only
> > > > use
> > > > > > linked databases as a last resort. I suggest using a shared
> > datasource
> > > > in RS
> > > > > > to Oracle instead. For straight reporting I never use linked
> > > databases.
> > > > It
> > > > > > complicates matters and buys you very little. What I do is have
a
> > > shared
> > > > > > datasource that has a readonly user credential stored by RS.
This
> > > takes
> > > > > > advantage of connection pooling and will be either much faster
or
> > much
> > > > > > easier or both than using linked databases.
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Bruce Loehle-Conger
> > > > > > MVP SQL Server Reporting Services
> > > > > >
> > > > > > "saustin99" <saustin99@.discussions.microsoft.com> wrote in
message
> > > > > > news:A948FA70-BB51-401E-B7B5-442340F37F16@.microsoft.com...
> > > > > > > Hi,
> > > > > > > Have the following sql analyzer query which takes 55
seconds to
> > > > return
> > > > > > > data.
> > > > > > > SELECT i.TRANSACTION_DATE,
> > > > > > > i.TYPE,
> > > > > > > i.PART_ID,
> > > > > > > i.WAREHOUSE_ID,
> > > > > > > i.TRANSACTION_ID,
> > > > > > > i.WORKORDER_BASE_ID,
> > > > > > > i.QTY,
> > > > > > > i.CLASS
> > > > > > > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > > > > > > WHERE i.TRANSACTION_ID = 526123 and i.CLASS = 'R' and i.TYPE ='I'
> > > > > > > order by i.TRANSACTION_ID
> > > > > > >
> > > > > > > Remove the and's and the response goes down to 1 second.
> > > > > > > SELECT i.TRANSACTION_DATE,
> > > > > > > i.TYPE,
> > > > > > > i.PART_ID,
> > > > > > > i.WAREHOUSE_ID,
> > > > > > > i.TRANSACTION_ID,
> > > > > > > i.WORKORDER_BASE_ID,
> > > > > > > i.QTY,
> > > > > > > i.CLASS
> > > > > > > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > > > > > > WHERE i.TRANSACTION_ID = 526123
> > > > > > > order by i.TRANSACTION_ID
> > > > > > >
> > > > > > > The question is why ? If you look at the data in the single
row
> > > > > > returned...
> > > > > > > 2004-10-22 00:00:00.000 I 76307-03005-061 QA ACCEPTED
> > > > 526123
> > > > > > > 17408 1.0000 R
> > > > > > > the record clearly contains the correct data. Any insight on
this
> > > > GREATLY
> > > > > > > appreciated because we have a reporting services query based
on a
> > > date
> > > > > > range
> > > > > > > which also has the same poor response time. If you put the
same
> > > query
> > > > into
> > > > > > an
> > > > > > > Oracle sql Plus query the data returns instantly.
> > > > > > >
> > > > > > > Thanks, Steve.
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > >
> > >
> >
> >
> >

Oracle OBJECTs

Hello,

Has any one used object-oriented features of PL/SQL?

I know you can do something like the following.

CREATE TYPE employee AS OBJECT (...)

My biggest concern is performance: Do the objects in Oracle negatively affect performance? Anything else I should be aware of?

Thanks in advance,
EdwardHello Edward,

when you use TYPE for accessing or handling datas the performance will not change. Thats what we find out in our projects.
The benefit of using types is accesing complex datas very handy and often with one select.

Regards

Manfred Peter
(Alligator Company)
http://www.alligatorsql.com