I have a couple custom database that I am building. They will be 3NF with several many to many linking tables.
In general, both databases will have about 30 tables. MOST tables will have an autonumber PK, and two FK fields related to PK's on another table. My question is about indexing...
I will not be designing the application(s) using the DB's (as far as ASP code, VB script) etc, so I am not familiar with the SQL to be used, nor am I confident that my developers will run the code by me before the system is done.
Therefore, I am trying to create very general indexes on each table that will serve generic needs (my attempt at half-way optimizing the DB). So each table has a unique index on the PK, and each FK has it OWN non-unique index. Is this the correct way to do this? Or, should I have one index that covers both FK columns rather than two indexes? Any thoughts? Please provide some reasoning with any answers.
Thanks in advance!
Ryan HuntRun the CPU intensive statements through the Index Tuning Wizard (ITW) in order to get any index recommendations that may be necessary.
Review information from this http://www.sql-server-performance.com/mr_indexing.asp link on choosing indexes.
2012年3月19日星期一
2012年3月7日星期三
First Timer Having an Insert Problem
Ok...I am stumped
I am currently writing a custom DTS package using an ActiveX script. This is my first time writing an ActiveX script and using the VBscripting language.
What I need to do is take data from three existing MS SQL Server 2k tables and import it into a single new table after serveral numerical data manipulations.
The specific problem I am having is that when I insert my recorsets into the new table several of the records are out of their sequential order. I am confused because if I cut down on the amount of data I insert, either in the number of rows and/or number of columns, I have no problem with my insert, but when I insert all the data I need things get out of order and swap places. I am inserting using what I think is called a connection/execute command with TSQL commands, and from what I have read this is the most efficient way to go about it.
I have approximately 4800 rows to insert with 6 columns, but my program seems to error with I try to insert in excess of 4100 rows.
Does anyone have any ideas? I was told that it might have something to do with a buffer, but I have not been able to find any helpful documentation. I have included the code for my insert loop.
Thank you in Advance!
-TRoche
do until GPSxRecord.EOF
GPSx = GPSxRecord.Fields ("GPS_x").value
tx = GPSxRecord.Fields ("tx").value
GPSy = GPSyRecord.Fields ("GPS_y").value
ty = GPSyRecord.Fields ("ty").value
GPSz = GPSzRecord.Fields ("GPS_z").value
tz = GPSzRecord.Fields ("tz").value
'Executing the Insert Command
DestCmd = "INSERT INTO GPSIMPORT VALUES ( " & tx & ", " & GPSx & ", " & ty & ", " & GPSy & ", " & tz & ", " & GPSz & ") "
DestCon.Execute DestCmd
GPSxRecord.MoveNext
GPSyRecord.MoveNext
GPSzRecord.MoveNext
LoopDo you have a primary key on GPSIMPORT? If not, then your data structure is known as a "heap" and SQL server makes no guarantees about the order in which data is stored, or even the order in which it is retrieved in consecutive statements.
It is not a good idea to rely on the order in which data is inserted to be the order in which it is kept or retrieved. Define a primary key for your data.
blindman|||Thanks a lot Blindman!!
This seems to have worked!! I would have never found that solution.
thanks again,
TRoche|||"First timer having an insert problem."?
Maybe that's why you always remember your first....
I am currently writing a custom DTS package using an ActiveX script. This is my first time writing an ActiveX script and using the VBscripting language.
What I need to do is take data from three existing MS SQL Server 2k tables and import it into a single new table after serveral numerical data manipulations.
The specific problem I am having is that when I insert my recorsets into the new table several of the records are out of their sequential order. I am confused because if I cut down on the amount of data I insert, either in the number of rows and/or number of columns, I have no problem with my insert, but when I insert all the data I need things get out of order and swap places. I am inserting using what I think is called a connection/execute command with TSQL commands, and from what I have read this is the most efficient way to go about it.
I have approximately 4800 rows to insert with 6 columns, but my program seems to error with I try to insert in excess of 4100 rows.
Does anyone have any ideas? I was told that it might have something to do with a buffer, but I have not been able to find any helpful documentation. I have included the code for my insert loop.
Thank you in Advance!
-TRoche
do until GPSxRecord.EOF
GPSx = GPSxRecord.Fields ("GPS_x").value
tx = GPSxRecord.Fields ("tx").value
GPSy = GPSyRecord.Fields ("GPS_y").value
ty = GPSyRecord.Fields ("ty").value
GPSz = GPSzRecord.Fields ("GPS_z").value
tz = GPSzRecord.Fields ("tz").value
'Executing the Insert Command
DestCmd = "INSERT INTO GPSIMPORT VALUES ( " & tx & ", " & GPSx & ", " & ty & ", " & GPSy & ", " & tz & ", " & GPSz & ") "
DestCon.Execute DestCmd
GPSxRecord.MoveNext
GPSyRecord.MoveNext
GPSzRecord.MoveNext
LoopDo you have a primary key on GPSIMPORT? If not, then your data structure is known as a "heap" and SQL server makes no guarantees about the order in which data is stored, or even the order in which it is retrieved in consecutive statements.
It is not a good idea to rely on the order in which data is inserted to be the order in which it is kept or retrieved. Define a primary key for your data.
blindman|||Thanks a lot Blindman!!
This seems to have worked!! I would have never found that solution.
thanks again,
TRoche|||"First timer having an insert problem."?
Maybe that's why you always remember your first....
First Timer having an INSERT Problem
Ok I am stumped....
I am currently writing a custom DTS task through the DTS designer and an ActiveX script. The goal of my Script is to access 3 tables which already exist in the DB, extract and numerically manipulate data from each table, and then insert this data into a new table.
This is my first time writing an ActiveX script and my first time using the VB scripting language, so most of my script is based on examples I could find.
To be more exact about my problem here is the description. I created six recordsets from the data I extracted from the 3 tables. When I attempt to insert all the data into my new table some of the data inserts in the wrong order. Three of my columns are time columns, so it is easy to see where the data falls out of its intended sequential order. It is also easy to see that this happens at the exact same time value, every time I run the code.
I am confused because if I write a for loop and insert less data either through fewer columns or fewer rows, my code seems to work perfectly. I have approximately 4800 rows total to insert, but my code seems to mess up if I try and insert in excess of 4100 rows. Someone suggested that I may be exceeding my allowed buffer, but I don't really know what this means or how to correct it.
Becasue my data inserts correctly if I only insert, say 2 columns, but all the rows, I think that the recordsets are being creatred correctly, and that the fault lies in my insert loop.
I have included the script for my insert loop and an example of how the data looks when it is in error.
I am very grateful for any help you might be able to provide and please let me thank you in advance for you time!
-TRoche
do until GPSxRecord.EOF
GPSx = GPSxRecord.Fields ("GPS_x").value
tx = GPSxRecord.Fields ("tx").value
GPSy = GPSyRecord.Fields ("GPS_y").value
ty = GPSyRecord.Fields ("ty").value
GPSz = GPSzRecord.Fields ("GPS_z").value
tz = GPSzRecord.Fields ("tz").value
'Executing the Insert Command
DestCmd = "INSERT INTO GPSIMPORT VALUES ( " & tx & ", " & GPSx & ", " & ty & ", " & GPSy & ", " & tz & ", " & GPSz & ") "
DestCon.Execute DestCmd
GPSxRecord.MoveNext
GPSyRecord.MoveNext
GPSzRecord.MoveNext
Loop
Time(x) GPSx Time(y) GPS(y)
58.9447 383421.96 58.94497 1213470.912 58.94526 488
58.99134 383421.959 58.99162 1213470.912 58.99191 489
59.04329 383421.957 59.04356 1213470.912 59.04385 490
59.0951 383421.956 59.09538 1213470.912 59.09566 490
59.14204 383421.955 59.14231 1213470.912 59.1426 490
221.19337 383447.07 221.19364 1213349.901 221.19393 479
221.24045 383447.069 221.24072 1213349.895 221.241 479
221.29253 383447.068 221.2928 1213349.889 221.29308 479
221.34434 383447.067 221.34461 1213349.883 221.3449 478'************************************************* **********
' Visual Basic ActiveX Script
'************************************************* **********
Function Main()
dim ConnSQL1 ' SQL Server connection
dim RSSQL ' SQL Server recordset
dim strSQL ' SQL String
dim rc
' SET DATA HANDLING OBJECTS
set ConnSQL1 = CreateObject("ADODB.Connection")
set RSSQL = CreateObject("ADODB.Recordset")
'OPEN DATA CONNECTION
ConnSQL1.Open = "Provider=SQLOLEDB;Data Source=server;Initial Catalog=database;UID=username;Password=asdf"
'create a select statement and put into temp table in the
'format that you are after from the three tables
'I assume you can do that
'Once you have all those records, then just insert them
'from the temp table
strSQL = "SELECT * into #temp from table " & _
"insert into newtable(f1, f2, f3, f4)" & _
"select f1, f2, f3, f4 from #temp"
RSSQL.Open strSQL, ConnSQL1
ConnSQL1.execute strSQL
RSSQL.close
Main = rc
End Function
I am currently writing a custom DTS task through the DTS designer and an ActiveX script. The goal of my Script is to access 3 tables which already exist in the DB, extract and numerically manipulate data from each table, and then insert this data into a new table.
This is my first time writing an ActiveX script and my first time using the VB scripting language, so most of my script is based on examples I could find.
To be more exact about my problem here is the description. I created six recordsets from the data I extracted from the 3 tables. When I attempt to insert all the data into my new table some of the data inserts in the wrong order. Three of my columns are time columns, so it is easy to see where the data falls out of its intended sequential order. It is also easy to see that this happens at the exact same time value, every time I run the code.
I am confused because if I write a for loop and insert less data either through fewer columns or fewer rows, my code seems to work perfectly. I have approximately 4800 rows total to insert, but my code seems to mess up if I try and insert in excess of 4100 rows. Someone suggested that I may be exceeding my allowed buffer, but I don't really know what this means or how to correct it.
Becasue my data inserts correctly if I only insert, say 2 columns, but all the rows, I think that the recordsets are being creatred correctly, and that the fault lies in my insert loop.
I have included the script for my insert loop and an example of how the data looks when it is in error.
I am very grateful for any help you might be able to provide and please let me thank you in advance for you time!
-TRoche
do until GPSxRecord.EOF
GPSx = GPSxRecord.Fields ("GPS_x").value
tx = GPSxRecord.Fields ("tx").value
GPSy = GPSyRecord.Fields ("GPS_y").value
ty = GPSyRecord.Fields ("ty").value
GPSz = GPSzRecord.Fields ("GPS_z").value
tz = GPSzRecord.Fields ("tz").value
'Executing the Insert Command
DestCmd = "INSERT INTO GPSIMPORT VALUES ( " & tx & ", " & GPSx & ", " & ty & ", " & GPSy & ", " & tz & ", " & GPSz & ") "
DestCon.Execute DestCmd
GPSxRecord.MoveNext
GPSyRecord.MoveNext
GPSzRecord.MoveNext
Loop
Time(x) GPSx Time(y) GPS(y)
58.9447 383421.96 58.94497 1213470.912 58.94526 488
58.99134 383421.959 58.99162 1213470.912 58.99191 489
59.04329 383421.957 59.04356 1213470.912 59.04385 490
59.0951 383421.956 59.09538 1213470.912 59.09566 490
59.14204 383421.955 59.14231 1213470.912 59.1426 490
221.19337 383447.07 221.19364 1213349.901 221.19393 479
221.24045 383447.069 221.24072 1213349.895 221.241 479
221.29253 383447.068 221.2928 1213349.889 221.29308 479
221.34434 383447.067 221.34461 1213349.883 221.3449 478'************************************************* **********
' Visual Basic ActiveX Script
'************************************************* **********
Function Main()
dim ConnSQL1 ' SQL Server connection
dim RSSQL ' SQL Server recordset
dim strSQL ' SQL String
dim rc
' SET DATA HANDLING OBJECTS
set ConnSQL1 = CreateObject("ADODB.Connection")
set RSSQL = CreateObject("ADODB.Recordset")
'OPEN DATA CONNECTION
ConnSQL1.Open = "Provider=SQLOLEDB;Data Source=server;Initial Catalog=database;UID=username;Password=asdf"
'create a select statement and put into temp table in the
'format that you are after from the three tables
'I assume you can do that
'Once you have all those records, then just insert them
'from the temp table
strSQL = "SELECT * into #temp from table " & _
"insert into newtable(f1, f2, f3, f4)" & _
"select f1, f2, f3, f4 from #temp"
RSSQL.Open strSQL, ConnSQL1
ConnSQL1.execute strSQL
RSSQL.close
Main = rc
End Function
2012年2月19日星期日
Firefox(2?) report rendering problems
Hi, recently added a reportviewer control to a page and it rendered
incorrectly in Firefox 2. So we made a custom report control that calls the
reporting services and renders in HTML 4.0 and the formatting is still messed
up. Basically everything is crammed into the left 5% or so of the page so
all of the text is a jumbled mess. Has anyone tried their reports in Firefox
2 and had success? Anyone encountered this same problem? Thanks!
--
Brian Orrell
Pariveda SolutionsYes, we have the same problems:
Fixed some of them based on info found here:
http://weblogs.asp.net/jgalloway/archive/2006/09/01/SQL-Reporting-Services-_2D00_-CSS-fix-for-Firefox.aspx
Steve MunLeeuw
"Brian Orrell" <BrianOrrell@.community.nospam> wrote in message
news:D5DF5511-A10F-4C87-B584-943798C02E0A@.microsoft.com...
> Hi, recently added a reportviewer control to a page and it rendered
> incorrectly in Firefox 2. So we made a custom report control that calls
> the
> reporting services and renders in HTML 4.0 and the formatting is still
> messed
> up. Basically everything is crammed into the left 5% or so of the page so
> all of the text is a jumbled mess. Has anyone tried their reports in
> Firefox
> 2 and had success? Anyone encountered this same problem? Thanks!
> --
> Brian Orrell
> Pariveda Solutions|||I've been using this:
https://addons.mozilla.org/firefox/1419/
Kevin
"Brian Orrell" <BrianOrrell@.community.nospam> wrote in message
news:D5DF5511-A10F-4C87-B584-943798C02E0A@.microsoft.com...
> Hi, recently added a reportviewer control to a page and it rendered
> incorrectly in Firefox 2. So we made a custom report control that calls
> the
> reporting services and renders in HTML 4.0 and the formatting is still
> messed
> up. Basically everything is crammed into the left 5% or so of the page so
> all of the text is a jumbled mess. Has anyone tried their reports in
> Firefox
> 2 and had success? Anyone encountered this same problem? Thanks!
> --
> Brian Orrell
> Pariveda Solutions
incorrectly in Firefox 2. So we made a custom report control that calls the
reporting services and renders in HTML 4.0 and the formatting is still messed
up. Basically everything is crammed into the left 5% or so of the page so
all of the text is a jumbled mess. Has anyone tried their reports in Firefox
2 and had success? Anyone encountered this same problem? Thanks!
--
Brian Orrell
Pariveda SolutionsYes, we have the same problems:
Fixed some of them based on info found here:
http://weblogs.asp.net/jgalloway/archive/2006/09/01/SQL-Reporting-Services-_2D00_-CSS-fix-for-Firefox.aspx
Steve MunLeeuw
"Brian Orrell" <BrianOrrell@.community.nospam> wrote in message
news:D5DF5511-A10F-4C87-B584-943798C02E0A@.microsoft.com...
> Hi, recently added a reportviewer control to a page and it rendered
> incorrectly in Firefox 2. So we made a custom report control that calls
> the
> reporting services and renders in HTML 4.0 and the formatting is still
> messed
> up. Basically everything is crammed into the left 5% or so of the page so
> all of the text is a jumbled mess. Has anyone tried their reports in
> Firefox
> 2 and had success? Anyone encountered this same problem? Thanks!
> --
> Brian Orrell
> Pariveda Solutions|||I've been using this:
https://addons.mozilla.org/firefox/1419/
Kevin
"Brian Orrell" <BrianOrrell@.community.nospam> wrote in message
news:D5DF5511-A10F-4C87-B584-943798C02E0A@.microsoft.com...
> Hi, recently added a reportviewer control to a page and it rendered
> incorrectly in Firefox 2. So we made a custom report control that calls
> the
> reporting services and renders in HTML 4.0 and the formatting is still
> messed
> up. Basically everything is crammed into the left 5% or so of the page so
> all of the text is a jumbled mess. Has anyone tried their reports in
> Firefox
> 2 and had success? Anyone encountered this same problem? Thanks!
> --
> Brian Orrell
> Pariveda Solutions
订阅：
博文 (Atom)