Tree structre table performance problem
-
Hi Any body. I Create a table for location that contains city, state and country in tree structure(parent child). User add Post in "post" table and ech post has a location id thac can be a city or state or country. Here is my questions: 1-If I use correct Solution for save location or I should separate city and state and country in another tables? 2-Whats best solution with high performance to show city,state and country for each post.Example: post id city state country ---- ---- ---- ------ --------- some text 4 waco txas us some text 4 null txas us some text 4 null null us I use a view with 3 join to show all location here is my view :
SELECT Citeis.name AS city, States.name AS state, Cuntries.name AS country, Cuntries.id AS CountryID, States.id AS StateID, Citeis.id AS cityID
FROM dbo.Geo AS Citeis INNER JOIN
dbo.Geo AS States ON Citeis.parentID = States.id INNER JOIN
dbo.Geo AS Cuntries ON States.parentID = Cuntries.idAnd then I join it to post table:
SELECT
dbo.Post.*, dbo.ViewStates.city, dbo.ViewStates.state, dbo.ViewStates.countryFROM dbo.Post INNER JOIN
dbo.ViewStates ON dbo.Post.geoID = dbo.ViewStates.cityIDBut my host don't allow me to execute this query because of it performance.It say that ths query consume some resource. Should I use function to retrieve state and city and country ? should I use inline function or table function? Thanks for any idea.
sepel
-
Hi Any body. I Create a table for location that contains city, state and country in tree structure(parent child). User add Post in "post" table and ech post has a location id thac can be a city or state or country. Here is my questions: 1-If I use correct Solution for save location or I should separate city and state and country in another tables? 2-Whats best solution with high performance to show city,state and country for each post.Example: post id city state country ---- ---- ---- ------ --------- some text 4 waco txas us some text 4 null txas us some text 4 null null us I use a view with 3 join to show all location here is my view :
SELECT Citeis.name AS city, States.name AS state, Cuntries.name AS country, Cuntries.id AS CountryID, States.id AS StateID, Citeis.id AS cityID
FROM dbo.Geo AS Citeis INNER JOIN
dbo.Geo AS States ON Citeis.parentID = States.id INNER JOIN
dbo.Geo AS Cuntries ON States.parentID = Cuntries.idAnd then I join it to post table:
SELECT
dbo.Post.*, dbo.ViewStates.city, dbo.ViewStates.state, dbo.ViewStates.countryFROM dbo.Post INNER JOIN
dbo.ViewStates ON dbo.Post.geoID = dbo.ViewStates.cityIDBut my host don't allow me to execute this query because of it performance.It say that ths query consume some resource. Should I use function to retrieve state and city and country ? should I use inline function or table function? Thanks for any idea.
sepel
You have two problems here, first off storing the city, state & country on the post table. What happens if you find you have data issues and have to move a city to another state - will require update to the post table. Second problem is performance of your query. I would suggest three tables, Country, State, City, with city having a foreign key of state etc. Then just keep the city location id on the post table and join to the other two tables to get your state and country. Either that or keep the parent child relationship in location and use a CTE (common table expression) to recursively query the table to get the parent relationships for a particular location. Then just store a single location id as the foreign key on the post table. This has the possible advantage of a post being able to be logged against a country or a state instead of just a city.
-
Hi Any body. I Create a table for location that contains city, state and country in tree structure(parent child). User add Post in "post" table and ech post has a location id thac can be a city or state or country. Here is my questions: 1-If I use correct Solution for save location or I should separate city and state and country in another tables? 2-Whats best solution with high performance to show city,state and country for each post.Example: post id city state country ---- ---- ---- ------ --------- some text 4 waco txas us some text 4 null txas us some text 4 null null us I use a view with 3 join to show all location here is my view :
SELECT Citeis.name AS city, States.name AS state, Cuntries.name AS country, Cuntries.id AS CountryID, States.id AS StateID, Citeis.id AS cityID
FROM dbo.Geo AS Citeis INNER JOIN
dbo.Geo AS States ON Citeis.parentID = States.id INNER JOIN
dbo.Geo AS Cuntries ON States.parentID = Cuntries.idAnd then I join it to post table:
SELECT
dbo.Post.*, dbo.ViewStates.city, dbo.ViewStates.state, dbo.ViewStates.countryFROM dbo.Post INNER JOIN
dbo.ViewStates ON dbo.Post.geoID = dbo.ViewStates.cityIDBut my host don't allow me to execute this query because of it performance.It say that ths query consume some resource. Should I use function to retrieve state and city and country ? should I use inline function or table function? Thanks for any idea.
sepel
The first question is that is your indexing correctly (on all ID columns and ParentID columns). The other question is that do you really want to retrieve all rows. You propably have somekind of where clause when you query the data. You should also build indexes to support that query. Just a sidenote, you don't have to have 3 table. If the columns in all tables are (almost) the same, yuo can combine the data into 1 table. However, I think that's not where you should start from.
The need to optimize rises from a bad design.My articles[^]