February 23, 2010 at 10:25 am
I've spent almost all day figuring out how to solve this puzzle... and still searching...
I'm using SQL Server 2008 which is connected to a Mysql (through linked server). That linked server worked perfectly in a test environment. When switching to the 'actual' database, I've encountered a performance drop which is not acceptable. It makes sense, because I've read that a linked server (OPENQUERY) is going to do a 'select * from collection' and the filtering and narrowing down happens on the SQL server. The C# application - using NHibernate - is going into timeout.
What have I tried so far?
Option A:
- I've created a view on the SQL server (which queries the linked server using OPENROWSET - select * from collection - this takes forever)
- created a function which narrows down the view by passing the unique id - this takes too long - 22 seconds!
Option B:
- Plain create a query using openquery with HARDcoded query
SELECT *
FROM OPENQUERY(DATASOURCE, 'select * from reservationViewOnLinkedServer where unique_pid="3508091145"');
Option B is mighty fast, but I'm unable to do this in a UDF.
This query isn't meant for ad-hoc or reporting purposes. It should be ready under 2 seconds.
Do you have any other ideas I might pursue? Thx!!!
February 23, 2010 at 10:31 am
Warning, I haven't used MySQL in this configuration so mileage may vary..
However, have you looked at the query plan for a representative query (or better yet the same query) on both servers to see if it is generating a difference in the plan. Are they both connecting to the same server? Is the linked server EXACTLY defined the same, all visible options?
These are just the thoughts off the top of my head.
CEWII
February 23, 2010 at 11:42 am
That's exactly what I like about linked servers or OpenQuery stuff :sick::sick:
So, that's why always advise to try to avoid them in the first place.
- Check for differences in the linked server definition and settings between your test and prod sqlinstance.
- Is you test instance connected to the same MySQL db ?
If not, you cannot compare !
- off course, your linked servers performance is dependent on the pressure of your linked server (MySQL) !
And as you have noticed correctly, in many cases SQLServer just pulls over all data before filtering !
And because SQLServer is the "middleware" it will always be blamed for the slow performance !
btw Did you try the SQL Server Migration Assistant for MySQL :rolleyes: http://blogs.technet.com/dataplatforminsider/archive/2010/01/11/free-download-microsoft-sql-server-migration-assistant.aspx
It my even help you solve your linked server issue(s) ,if you try it on a test instance and actually can get a look regarding how you should define your variables for usage with mysql.
So if your application doesn't actually need to JOIN the data comming from SQLServer with de data comming from MySQL, have your application modified (business layer or data-tier) so it uses separate connections and natively queries both engines !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 23, 2010 at 11:54 am
Thx you for the responses so far!
I want to point out that my test environment hasn't changed that much. The only thing I've changed (on the MySQL server side) is the selection of another MySql database. All other settings hasn't been fiddled with.
Maybe I'm off with my inspiration, but I'm still unsure how to fix this. I still want to use NHibernate as ORM AND execute query on the MySql side.
February 23, 2010 at 11:58 am
Superflokky (2/23/2010)
Thx you for the responses so far!I want to point out that my test environment hasn't changed that much. The only thing I've changed (on the MySQL server side) is the selection of another MySql database. All other settings hasn't been fiddled with.
Maybe I'm off with my inspiration, but I'm still unsure how to fix this. I still want to use NHibernate as ORM AND execute query on the MySql side.
I haven't worked with NHibernate :blink: so this may be way off ....
Can't you just query your MySQL directly from NHibernate ?? (without using SQLServer as gateway)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 23, 2010 at 12:04 pm
ALZDBA (2/23/2010)
I haven't worked with NHibernate :blink: so this may be way off ....
Can't you just query your MySQL directly from NHibernate ?? (without using SQLServer as gateway)
NHibernate is being set-up with a specified datasource - which is in this case SQL Server. All HQL (Hibernate Queries) are 'translated' to T-SQL (to SQL Server dialect). So no, that's not possible. That's the reason in the first place why we started with a Linked Server anyway... One single point of datasource.. It sounded to nice to be true 🙁
February 24, 2010 at 12:37 am
Thank you for the feedback.
That's a bummer :ermm:
Isn't each db engine like an "add on" for NHibernate ?
So maybe just adding MySQL in the config could do the trick.
My colleague just pointed me to this ref for multiple db engine support of NHibernate :
http://www.codeproject.com/KB/aspnet/NHibernateMultipleDBs.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 24, 2010 at 1:43 am
All I really want to do is perform something like this:
SELECT *
FROM OPENQUERY(DATASOURCE, 'select * from myView where unique_pid=@pid');
So I can do something like
select * from xxx where pid ='3508091145');
This must be possible right?
February 24, 2010 at 2:01 am
You should indeed be able to define a linked server from sql2008 to mysql.
However, If you also want performance, that may not be your best choice !
If you don't need to actually JOIN mysql data with SQL data, have your ORM configured to use the correct driver set and perform the query directly on mysql.
Old fashioned, but performant :w00t:
BTW: Isn't that what any ORM tool is all about ? write the query you want and have the ORM translate it to your dbms code ??
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply