I have a query like:
select * from tableA where id in (1,2,3,..)List of ids ids very long about 200 000 ids.
There is an clustered index on id colums.
The same query on mysql rus fast but on MS it cant take about 20 minuts (on mysql takes few second).
How can I resolve this?
Well my first suggestion would be to run mysql on windows instead of MS SQLServer. But barring that is the field indexed in MS SQL server?
Well my first suggestion would be to run mysql on windows instead of MS SQLServer. But barring that is the field indexed in MS SQL server?
It's not mine decsion to use MS SQL. There is a proper index on id (execution plan show that index was used), I noticed that query runs fast when MS use nested loops to search (up to several thousand of ids) but when there is about 100 000 ids MS use hash match and its very slow.
Is it possible to set up a separate table with the ID's or to flag the records in the database somehow. You have to get the list from somewhere so why not set up a table with the ID's and do a join off of the ID.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.