June 9, 2008 at 6:07 am
I have created a Linked Server using a proprietary ODBC driver (simbalink to an IEX database!!).
When I use the Linked Server in a normal query to return data it will take 2m32s to return 14,658 rows, issuing same query through OPENQUERY (pass through query) same data returns in 21 seconds.
Queries are :-
select *
from iexdata."/usr/prms/run/db/statdb"..schedheader
where customer_id = 1
and modify_date_time >= '2008-06-04 15:56:00'
and date >= '2008-05-04 00:00:00'
and
select *
from openquery([iexdata],
'select *
from schedheader
where customer_id = 1
and date >= ''2008-05-04 00:00:00''
and modify_date_time >= ''2008-06-04 15:56:00''')
Now I am banging my head against a break wall, basically we are using the SQL server instance as a middle-tier to get data to an oracle database. We have HS links set up from ORACLE to views on SQL Server that point to IEX using the linked server query.
Performance is an issue as we only have a certain window to get data.
So :-
a) Why is the linked server query not using the indexes from the IEX database, I have tried setting the 'Index as Access Path' option on the Provider bith no joy.
b) Anyone got any suggestion on how I can do an HS query from ORACLE to a SQL table function that would return the data I want??
Cheers
Bob
June 11, 2008 at 8:22 am
OK, i have got around the performance problem by using pass through queries using OPENQUERY. What I have done is create an sp on the database that builds a dynamic SQL string based on variables passed in
ALTER procedure [dbo].[sp_Execute_PassThrough_Query]
@vTableName varchar(100),
@dLastLoadDate datetime,
@dMaxLoadDate datetime = null
as
declare @sql nvarchar(4000)
-- build the openquery statement dynamically, unfortunately openquery
-- does not accept a varchar variable as a parameter!
set @sql = 'select * from openquery(IEXDATA, '''
set @sql = @sql + 'select * from ' + @vTableName + ' '
set @sql = @sql + 'where customer_id = 1 '
if (@vTableName = 'schedheader' or @vTableName = 'activityheader') begin
set @sql = @sql + 'and date > ''''' + convert(varchar, dateadd(mm, -1, @dLastLoadDate), 120) + ''''' '
end
if (@vTableName = 'result' or @vTableName = 'forecast') begin
set @sql = @sql + 'and date_time > ''''' + convert(varchar, dateadd(mm, -1, @dLastLoadDate), 120) + ''''' '
end
set @sql = @sql + 'and modify_date_time > ''''' + convert(varchar, @dLastLoadDate, 120) + ''''''
-- check if MaxLoadDate is passed
if @dMaxLoadDate is not null begin
set @sql = @sql + ' and modify_date_time <= ''''' + convert(varchar, @dMaxLoadDate, 120) + ''''''
end
set @sql = @sql + ' '')'
exec sp_executesql @sql
This sp works fine when I call it from SQL Server, however calling from ORACLE using HS services was returning an error..either column 'Date' doesn't exist or column 'Date_Time' doesn't exist!!
I have eventually tracked this down to the fact that ORACLE HS pass throughs parse the statement first which has the effect of tacking SET FMTONLY ON and SET FMTONLY OFF at the start and end of the command.
ex
SET FMTONLY ON
exec sp_Execute_PassThrough_Query 'scheddetail' , '2008-05-16 12:40:00', '2008-06-11 12:21:00'
SET FMTONLY OFF
When running this in SQL server I get the same error returned as via ORACLE HS!!!!
It appears the FMTONLY is causing my Dynamic string to be built with all columns, even those in the IF conditions!!!
So, my question is, how do I stop those columns being added to the dynamic SQL when it is juts parsing the query??
June 12, 2008 at 4:21 am
must be talking to myself on this thread!!
I have found a solution. Apparently their is a bug with the SET FMTONLY ON statement.
see here
This has the effect that any conditional clauses in an SP are ignored.
So in my above example all set @sql = @sql + ..... commands are executed under SET FMTONLY ON!!
The workaround for this is quite simple. What I have done is at the end of the sp, just before the sp_executesql command I have added the following lines.
if 1 = 0 begin
set @sql = 'select * from openquery(IEXDATA, ''select * from ' + @vTableName + ' '')'
end
Basically, when run under FMTONLY ON, the "if 1=0 begin" will be ignored and @sql will be reset to just selecting from the table. Returning the metadata about the columns.
When run with FMTONLY off the 1=0 will never be true (in this universe at least), and @sql will never be reset!!
June 12, 2008 at 8:32 am
bobjbain (6/12/2008)
must be talking to myself on this thread!!
Would you rather have someone say something like... "I've never had such a requirement, never heard of such a problem, and therefor, can't help... have a nice day" ? 😉
Glad you're working it out and sharing with us. 🙂 Not being sarcastic, either. I sometimes have to work with linked servers to Oracle and, although I've not run into such a problem (not even close), I know where to look now if I do. Thank you.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 19, 2008 at 8:48 am
This was a complete Google "Hail Mary" for me on this very familiar topic.:w00t:
I work for a call center that uses IEX. We are using the TotalView SIMBA ODBC v6.0 client to connect to IEX, then run scripts to imports into a SQL server each day for Schedule header and detail information. We want to be able to poll the data for more than a day at a time, but have so many schedules coming back that the queries will time out beyond a day or so (for ScheduleDetail).
Our connection seems poor, so I am hesitant to used them, but wanted to try linked servers into our SQL 2005 box for those tables.
I had tried using the Microsoft OLE DB Provider for ODBC Drivers, and connecting there with the Data souce and provider string, but when I attempt to expand the Catalog, the SQL server is unable to obtain any info from DBSCHEMA.
What version of SIMBA are you using?
I think I am about 30 steps behind where you are at. =\
February 23, 2009 at 7:22 am
Hey there - I am also going to be doing items like this - using IEX and the Simba Client.
Have either one of you been able to determine which date/time field should be used in pulling current data related to Call Centers?
I am seeing begin_period, end_period, date_time, gen_date_time and can't seem to get a grasp on which it is that I should narrow down to.
I want to pull from Forecast - the Calls Forecasted, Results, the results for the day, linking using ct_id and from what i can see date_time.
If you can let me know I would appreciate it!
February 23, 2009 at 8:36 am
Sorry mo - I can't really be any help on this. This project got put on the "back burner" for a while, so we have had no progress with using the linked server to IEX.
February 23, 2009 at 8:43 am
It all depends what you want and how your people have used the system.
I'm no IEX expert, only pull data from it, what we have works but might be different for you.
There is a field in Forecast called DATE_TIME which, according the the data dictionary is "Date and beginning time period (in 15 or 30-minute increments) for this forecast record."
This is the field I have used.
Do you have a DD? If not do you want me to email what I have to you??
February 23, 2009 at 6:36 pm
Thanks - after reviewing the query at the top of this post - I was able to determine that date_time in the forecast did the job. My query matched to the results I was looking for!
Thank you again for feedback.
February 27, 2009 at 5:54 am
Hey "BOB"
How the heck did you get your SQL Server to link to the IEX DB?
I am using the SIMBA Client - and SQL 2005?
M
October 12, 2009 at 10:13 am
SQL server can easily be connected to IEX DB using simba client thru DSN (I would recommend system DSN on this).
Once you have installed simba client, use linkserver sp on master db and call system dsn. This would built a link between local machine and IEX server.
Once link is established, user openquery in your sp to get data
October 17, 2009 at 2:47 pm
Lol you are using SIMBA and he telling you about SQl hahaha
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply