SQL insert query takes too much time in migration
I am migrating data from one un-normalized database to another normalized. I could migrate almost all the data but got to the point where a query lasts around 5 mins and I think its too much.
Here is the Entity-Relation Diagram:
Diagram of normalized database
And a picture of the un-normalized database:
Un-normalized database
The table that I want to complete where I have the problem is "Items" and the query is:
INSERT INTO LOS_CAPOS.Items (Item_Factura_Nro, Item_Compra_Cod, Item_Factura_Monto, Item_Factura_Cantidad, Item_Factura_Descripcion)
SELECT f.Factura_Nro, c.Compra_Cod, Item_Factura_Monto, Item_Factura_Cantidad, Item_Factura_Descripcion
FROM LOS_CAPOS.Facturas f
INNER JOIN gd_esquema.Maestra m ON f.Factura_Nro = m.Factura_Nro
INNER JOIN LOS_CAPOS.Compras c ON c.Compra_Fecha = m.Compra_Fecha AND c.Compra_Cantidad = m.Compra_Cantidad
Facturas is a 7664 rows and Compras is a 78327 rows table
Thanks!
sql
sql-server database-normalization edited Nov 18 '18 at 19:41
Sami
8,90831241
asked Nov 18 '18 at 19:38
Agustin MolesAgustin Moles
15
add a comment |
I am migrating data from one un-normalized database to another normalized. I could migrate almost all the data but got to the point where a query lasts around 5 mins and I think its too much.
Here is the Entity-Relation Diagram:
Diagram of normalized database
And a picture of the un-normalized database:
Un-normalized database
The table that I want to complete where I have the problem is "Items" and the query is:
INSERT INTO LOS_CAPOS.Items (Item_Factura_Nro, Item_Compra_Cod, Item_Factura_Monto, Item_Factura_Cantidad, Item_Factura_Descripcion)
SELECT f.Factura_Nro, c.Compra_Cod, Item_Factura_Monto, Item_Factura_Cantidad, Item_Factura_Descripcion
FROM LOS_CAPOS.Facturas f
INNER JOIN gd_esquema.Maestra m ON f.Factura_Nro = m.Factura_Nro
INNER JOIN LOS_CAPOS.Compras c ON c.Compra_Fecha = m.Compra_Fecha AND c.Compra_Cantidad = m.Compra_Cantidad
Facturas is a 7664 rows and Compras is a 78327 rows table
Thanks!
sql
sql-server database-normalization edited Nov 18 '18 at 19:41
Sami
8,90831241
asked Nov 18 '18 at 19:38
Agustin MolesAgustin Moles
15
I don't understand your question. Is it simply that theSELECTis too slow?
– Gordon Linoff
Nov 18 '18 at 20:00
Gordon Linoff has the point - you need to add if running the SELECT without INSERT is slow or not. Also, gd_esquema.Maestra is not on the diagram. I'd guess that LOS_CAPOS.Compras is not looked up optimally, as its PK is not in the join but rather 2 other columns and those might not even have an index, as well migth be the case with gd_esquema.Maestra.
– Dávid Laczkó
Nov 18 '18 at 20:15
Yes, the select query is slow. Maestra is the unnormalized table, I mean almost all the columns of all normalized tables of the diagram are in that table, where a lot of data are null or repeated. What index do you recommend?
– Agustin Moles
Nov 18 '18 at 20:38
See the query plan - the DB engine issues a missing index recommendation and that is usually the most efficient solution. Any query tuning/optimisation can only be done by seeing the query plan. And you need to know that an index will slow down INSERT/UPDATE/DELETE operations as the index needs to be updated as well. So there are different indexing scenarios for which table, which column, read vs write considerations, no ultimate solution exists that solves slowness.
– Dávid Laczkó
Nov 18 '18 at 20:51
So which index do you recommend on which columns to solve the problem?
– Agustin Moles
Nov 18 '18 at 21:17
add a comment |
I am migrating data from one un-normalized database to another normalized. I could migrate almost all the data but got to the point where a query lasts around 5 mins and I think its too much.
Here is the Entity-Relation Diagram:
Diagram of normalized database
And a picture of the un-normalized database:
Un-normalized database
The table that I want to complete where I have the problem is "Items" and the query is:
INSERT INTO LOS_CAPOS.Items (Item_Factura_Nro, Item_Compra_Cod, Item_Factura_Monto, Item_Factura_Cantidad, Item_Factura_Descripcion)
SELECT f.Factura_Nro, c.Compra_Cod, Item_Factura_Monto, Item_Factura_Cantidad, Item_Factura_Descripcion
FROM LOS_CAPOS.Facturas f
INNER JOIN gd_esquema.Maestra m ON f.Factura_Nro = m.Factura_Nro
INNER JOIN LOS_CAPOS.Compras c ON c.Compra_Fecha = m.Compra_Fecha AND c.Compra_Cantidad = m.Compra_Cantidad
Facturas is a 7664 rows and Compras is a 78327 rows table
Thanks!
sql
sql-server database-normalization edited Nov 18 '18 at 19:41
Sami
8,90831241
asked Nov 18 '18 at 19:38
Agustin MolesAgustin Moles
15
I am migrating data from one un-normalized database to another normalized. I could migrate almost all the data but got to the point where a query lasts around 5 mins and I think its too much.
Here is the Entity-Relation Diagram:
Diagram of normalized database
And a picture of the un-normalized database:
Un-normalized database
The table that I want to complete where I have the problem is "Items" and the query is:
INSERT INTO LOS_CAPOS.Items (Item_Factura_Nro, Item_Compra_Cod, Item_Factura_Monto, Item_Factura_Cantidad, Item_Factura_Descripcion)
SELECT f.Factura_Nro, c.Compra_Cod, Item_Factura_Monto, Item_Factura_Cantidad, Item_Factura_Descripcion
FROM LOS_CAPOS.Facturas f
INNER JOIN gd_esquema.Maestra m ON f.Factura_Nro = m.Factura_Nro
INNER JOIN LOS_CAPOS.Compras c ON c.Compra_Fecha = m.Compra_Fecha AND c.Compra_Cantidad = m.Compra_Cantidad
Facturas is a 7664 rows and Compras is a 78327 rows table
Thanks!
sql
sql-server database-normalization sql
sql-server database-normalization edited Nov 18 '18 at 19:41
Sami
8,90831241
asked Nov 18 '18 at 19:38
Agustin MolesAgustin Moles
15
edited Nov 18 '18 at 19:41
Sami
8,90831241
asked Nov 18 '18 at 19:38
Agustin MolesAgustin Moles
15
edited Nov 18 '18 at 19:41
Sami
8,90831241
edited Nov 18 '18 at 19:41
Sami
8,90831241
edited Nov 18 '18 at 19:41
Sami
8,90831241
8,90831241
asked Nov 18 '18 at 19:38
Agustin MolesAgustin Moles
15
asked Nov 18 '18 at 19:38
Agustin MolesAgustin Moles
15
asked Nov 18 '18 at 19:38
Agustin MolesAgustin Moles
15
15
I don't understand your question. Is it simply that theSELECTis too slow?
– Gordon Linoff
Nov 18 '18 at 20:00
Gordon Linoff has the point - you need to add if running the SELECT without INSERT is slow or not. Also, gd_esquema.Maestra is not on the diagram. I'd guess that LOS_CAPOS.Compras is not looked up optimally, as its PK is not in the join but rather 2 other columns and those might not even have an index, as well migth be the case with gd_esquema.Maestra.
– Dávid Laczkó
Nov 18 '18 at 20:15
Yes, the select query is slow. Maestra is the unnormalized table, I mean almost all the columns of all normalized tables of the diagram are in that table, where a lot of data are null or repeated. What index do you recommend?
– Agustin Moles
Nov 18 '18 at 20:38
See the query plan - the DB engine issues a missing index recommendation and that is usually the most efficient solution. Any query tuning/optimisation can only be done by seeing the query plan. And you need to know that an index will slow down INSERT/UPDATE/DELETE operations as the index needs to be updated as well. So there are different indexing scenarios for which table, which column, read vs write considerations, no ultimate solution exists that solves slowness.
– Dávid Laczkó
Nov 18 '18 at 20:51
So which index do you recommend on which columns to solve the problem?
– Agustin Moles
Nov 18 '18 at 21:17
add a comment |
I don't understand your question. Is it simply that theSELECTis too slow?
– Gordon Linoff
Nov 18 '18 at 20:00
Gordon Linoff has the point - you need to add if running the SELECT without INSERT is slow or not. Also, gd_esquema.Maestra is not on the diagram. I'd guess that LOS_CAPOS.Compras is not looked up optimally, as its PK is not in the join but rather 2 other columns and those might not even have an index, as well migth be the case with gd_esquema.Maestra.
– Dávid Laczkó
Nov 18 '18 at 20:15
Yes, the select query is slow. Maestra is the unnormalized table, I mean almost all the columns of all normalized tables of the diagram are in that table, where a lot of data are null or repeated. What index do you recommend?
– Agustin Moles
Nov 18 '18 at 20:38
See the query plan - the DB engine issues a missing index recommendation and that is usually the most efficient solution. Any query tuning/optimisation can only be done by seeing the query plan. And you need to know that an index will slow down INSERT/UPDATE/DELETE operations as the index needs to be updated as well. So there are different indexing scenarios for which table, which column, read vs write considerations, no ultimate solution exists that solves slowness.
– Dávid Laczkó
Nov 18 '18 at 20:51
So which index do you recommend on which columns to solve the problem?
– Agustin Moles
Nov 18 '18 at 21:17
I don't understand your question. Is it simply that the
SELECT is too slow?– Gordon Linoff
Nov 18 '18 at 20:00
I don't understand your question. Is it simply that the
SELECT is too slow?– Gordon Linoff
Nov 18 '18 at 20:00
Gordon Linoff has the point - you need to add if running the SELECT without INSERT is slow or not. Also, gd_esquema.Maestra is not on the diagram. I'd guess that LOS_CAPOS.Compras is not looked up optimally, as its PK is not in the join but rather 2 other columns and those might not even have an index, as well migth be the case with gd_esquema.Maestra.
– Dávid Laczkó
Nov 18 '18 at 20:15
Gordon Linoff has the point - you need to add if running the SELECT without INSERT is slow or not. Also, gd_esquema.Maestra is not on the diagram. I'd guess that LOS_CAPOS.Compras is not looked up optimally, as its PK is not in the join but rather 2 other columns and those might not even have an index, as well migth be the case with gd_esquema.Maestra.
– Dávid Laczkó
Nov 18 '18 at 20:15
Yes, the select query is slow. Maestra is the unnormalized table, I mean almost all the columns of all normalized tables of the diagram are in that table, where a lot of data are null or repeated. What index do you recommend?
– Agustin Moles
Nov 18 '18 at 20:38
Yes, the select query is slow. Maestra is the unnormalized table, I mean almost all the columns of all normalized tables of the diagram are in that table, where a lot of data are null or repeated. What index do you recommend?
– Agustin Moles
Nov 18 '18 at 20:38
See the query plan - the DB engine issues a missing index recommendation and that is usually the most efficient solution. Any query tuning/optimisation can only be done by seeing the query plan. And you need to know that an index will slow down INSERT/UPDATE/DELETE operations as the index needs to be updated as well. So there are different indexing scenarios for which table, which column, read vs write considerations, no ultimate solution exists that solves slowness.
– Dávid Laczkó
Nov 18 '18 at 20:51
See the query plan - the DB engine issues a missing index recommendation and that is usually the most efficient solution. Any query tuning/optimisation can only be done by seeing the query plan. And you need to know that an index will slow down INSERT/UPDATE/DELETE operations as the index needs to be updated as well. So there are different indexing scenarios for which table, which column, read vs write considerations, no ultimate solution exists that solves slowness.
– Dávid Laczkó
Nov 18 '18 at 20:51
So which index do you recommend on which columns to solve the problem?
– Agustin Moles
Nov 18 '18 at 21:17
So which index do you recommend on which columns to solve the problem?
– Agustin Moles
Nov 18 '18 at 21:17
add a comment |
1 Answer
1
active
oldest
votes
Start testing the SELECT only by commenting out a join (and the related columns coming from that table) and see which lookup is causing slowness. After that check if you can use other columns that are indexed to do the lookup. Ideally you would join LOS_CAPOS.Compras on its PK. If you can't, start testing as I mention below, by picking a column, create a non-clustered index, and test all SELECT/INSERT/UPDATE/DELETE operations on that table to see the impact.
Any query tuning/optimisation can only be done by seeing the query plan. And you need to know that an index will slow down INSERT/UPDATE/DELETE operations as the index needs to be updated as well. So there are different indexing scenarios for which table, which column, read vs write considerations, no ultimate solution exists that solves slowness.
answered Nov 19 '18 at 16:59
Dávid LaczkóDávid Laczkó
429128
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53364734%2fsql-insert-query-takes-too-much-time-in-migration%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Start testing the SELECT only by commenting out a join (and the related columns coming from that table) and see which lookup is causing slowness. After that check if you can use other columns that are indexed to do the lookup. Ideally you would join LOS_CAPOS.Compras on its PK. If you can't, start testing as I mention below, by picking a column, create a non-clustered index, and test all SELECT/INSERT/UPDATE/DELETE operations on that table to see the impact.
Any query tuning/optimisation can only be done by seeing the query plan. And you need to know that an index will slow down INSERT/UPDATE/DELETE operations as the index needs to be updated as well. So there are different indexing scenarios for which table, which column, read vs write considerations, no ultimate solution exists that solves slowness.
answered Nov 19 '18 at 16:59
Dávid LaczkóDávid Laczkó
429128
add a comment |
Start testing the SELECT only by commenting out a join (and the related columns coming from that table) and see which lookup is causing slowness. After that check if you can use other columns that are indexed to do the lookup. Ideally you would join LOS_CAPOS.Compras on its PK. If you can't, start testing as I mention below, by picking a column, create a non-clustered index, and test all SELECT/INSERT/UPDATE/DELETE operations on that table to see the impact.
Any query tuning/optimisation can only be done by seeing the query plan. And you need to know that an index will slow down INSERT/UPDATE/DELETE operations as the index needs to be updated as well. So there are different indexing scenarios for which table, which column, read vs write considerations, no ultimate solution exists that solves slowness.
answered Nov 19 '18 at 16:59
Dávid LaczkóDávid Laczkó
429128
add a comment |
Start testing the SELECT only by commenting out a join (and the related columns coming from that table) and see which lookup is causing slowness. After that check if you can use other columns that are indexed to do the lookup. Ideally you would join LOS_CAPOS.Compras on its PK. If you can't, start testing as I mention below, by picking a column, create a non-clustered index, and test all SELECT/INSERT/UPDATE/DELETE operations on that table to see the impact.
Any query tuning/optimisation can only be done by seeing the query plan. And you need to know that an index will slow down INSERT/UPDATE/DELETE operations as the index needs to be updated as well. So there are different indexing scenarios for which table, which column, read vs write considerations, no ultimate solution exists that solves slowness.
answered Nov 19 '18 at 16:59
Dávid LaczkóDávid Laczkó
429128
Start testing the SELECT only by commenting out a join (and the related columns coming from that table) and see which lookup is causing slowness. After that check if you can use other columns that are indexed to do the lookup. Ideally you would join LOS_CAPOS.Compras on its PK. If you can't, start testing as I mention below, by picking a column, create a non-clustered index, and test all SELECT/INSERT/UPDATE/DELETE operations on that table to see the impact.
Any query tuning/optimisation can only be done by seeing the query plan. And you need to know that an index will slow down INSERT/UPDATE/DELETE operations as the index needs to be updated as well. So there are different indexing scenarios for which table, which column, read vs write considerations, no ultimate solution exists that solves slowness.
answered Nov 19 '18 at 16:59
Dávid LaczkóDávid Laczkó
429128
answered Nov 19 '18 at 16:59
Dávid LaczkóDávid Laczkó
429128
answered Nov 19 '18 at 16:59
Dávid LaczkóDávid Laczkó
429128
answered Nov 19 '18 at 16:59
Dávid LaczkóDávid Laczkó
429128
429128
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53364734%2fsql-insert-query-takes-too-much-time-in-migration%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I don't understand your question. Is it simply that the
SELECTis too slow?– Gordon Linoff
Nov 18 '18 at 20:00
Gordon Linoff has the point - you need to add if running the SELECT without INSERT is slow or not. Also, gd_esquema.Maestra is not on the diagram. I'd guess that LOS_CAPOS.Compras is not looked up optimally, as its PK is not in the join but rather 2 other columns and those might not even have an index, as well migth be the case with gd_esquema.Maestra.
– Dávid Laczkó
Nov 18 '18 at 20:15
Yes, the select query is slow. Maestra is the unnormalized table, I mean almost all the columns of all normalized tables of the diagram are in that table, where a lot of data are null or repeated. What index do you recommend?
– Agustin Moles
Nov 18 '18 at 20:38
See the query plan - the DB engine issues a missing index recommendation and that is usually the most efficient solution. Any query tuning/optimisation can only be done by seeing the query plan. And you need to know that an index will slow down INSERT/UPDATE/DELETE operations as the index needs to be updated as well. So there are different indexing scenarios for which table, which column, read vs write considerations, no ultimate solution exists that solves slowness.
– Dávid Laczkó
Nov 18 '18 at 20:51
So which index do you recommend on which columns to solve the problem?
– Agustin Moles
Nov 18 '18 at 21:17