CODEWITHSUNDEEP
sqlsql-serverjoin
Kliver Max
Kliver Max

Reputation: 5299

JOINS works very slow

In my SQL-Server 2008 R2 i have a SQL query:

SELECT
IceCrossing.WaterwayName as WaterWayName,
IceCrossing.Segment_ID as Segment_ID,
the_geom = Track.Track
FROM dbo.IceCrossing
LEFT JOIN Track ON IceCrossing.Segment_ID=Track.Segment_ID

There i want to select all rows from IceCrossing and if in Track exists row with same Segment_ID show it in result. And there is problem with JOIN. Becouse its query works 4-5 seconds for return me my 260 rows. I was tried to change it:

SELECT
IceCrossing.WaterwayName as WaterWayName,
IceCrossing.Segment_ID as Segment_ID,
the_geom = Track.Track
FROM dbo.Track
RIGHT JOIN IceCrossing ON Track.Segment_ID=IceCrossing.Segment_ID

But same time.
Its possible to make it faster without make a any things with data base and table structures?

UPDATE

More info.
Track - 209 rows.
IceCrossing - 259 rows. Segment_ID type - [uniqueidentifier]
How to know about indexes on this?

UPDATE2

How i understand my problem in the the_geom field. Becouse query:

    SELECT
IceCrossing.WaterwayName as WaterWayName,
IceCrossing.Segment_ID as Segment_ID,
FROM dbo.IceCrossing
LEFT JOIN Track ON IceCrossing.Segment_ID=Track.Segment_ID

Works within a second.
the_geom type - geometry its like a very long string.
What can i do in this case?

Upvotes: 0

Views: 102

Answers (2)

roman
roman

Reputation: 117337

have your tried simple select * from Track, select * from IceCrossing? If you have huge amount of data in one of your columns (for example, varbinary(max)), it could be not the query that is slow, but receiving all data at client side.

Try that query

select
    I.Segment_ID,
    T.Segment_ID
from dbo.IceCrossing as I
    left outer join Track as T on T.Segment_ID = I.Segment_ID

How long does it executing?

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269493

The join is fine. You may need an index, either on Track(Segment_ID) or IceCrossing(Segment_ID).

With that volume of data, I'm surprised that the query could take so long. Have you run the query multiple times and gotten consistent results? Is anything else running on the server?

There is no difference in performance between the left outer join and right outer join. They do the same thing.

Upvotes: 3

Related Questions