|
Your queries are probably not optimised very well. It's very likely that you don't have a suitable index to use on a particular column, which means that the query optimiser decides it has to read every row ('Table Scan' or 'Clustered Index Scan' depending on whether or not you have a clustered index), which means that the time to execute increases with every row added.
I look in SQL Profiler to find the most costly queries, then use the Show Execution Plan feature in Query Analyzer to see what the optimiser did. The easiest thing to do to speed performance is to try to spot the table scans and add an index which contains the fields it's looking for in the WHERE clause.
Note that adding indexes will have an impact on INSERT, UPDATE and DELETE operations, due to the need to maintain the indexes. You need to balance this cost against the improvement in lookup times - not just on SELECTs but of course INSERT, UPDATE and DELETE all need to find records, so the indexes can help with this too.
If you're looking up on only part of a compound primary key, and the key fields you have don't include the top level of the key, consider re-ordering the fields in your key, or supplying the rest of the key information to the query.
If it's not fast enough after doing this, check for Bookmark Lookup operations. This is where SQL Server is cross-referencing between the index it used to find matches and either the base table or the clustered index, in order to check other fields in the WHERE clause that aren't in the index, or output data that you have in the SELECT clause. If this is happening you can help it out by adding the extra fields to the index as well.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
I use VCPP 6.0 and SQLExpress to connect to a database that contains 4 fields, one of the field holds the date and time. I want to be able to view entries between let's say 5 august 2005 21:30 and 8 august 23:30. I'm using the strFilter to run the queries but i got stuck when i was trying to use it on my date field. Should i separate the date from the time or smth? i think it's to late for my database.
|
|
|
|
|
Use stored procedures! (My fingers are getting cramped from writing that in this board)
in SQL you can
SELECT * FROM table WHERE effective_date BETWEEN @startDate AND @endDate
There are also methods to convert a date to a sting to make date only comparisons simple. Use yyyymmdd for this though.
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|
|
Ok, I knew about stored procedures but i was trying not to use them, because, how should i put this, i don't know i'm new to both sql and VC++ and i don't know how to use stored procedures.
|
|
|
|
|
Ignorance isn't an excuse for bad design. The fact that you know about them and don't want to learn how to use them in a systems makes this a travesty. (With apologies for the blunt honest truth; i've had a long weekend)
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|
|
I currently have this stored procedure:
<br />
@id as bigint,<br />
<br />
AS<br />
SELECT Meetings.meetinguser, Companies.name, Meetings.meetingdate <br />
FROM Companies INNER JOIN Meetings <br />
ON (Companies.id = Meetings.company) <br />
WHERE (Meetings.complete = 0)<br />
FOR XML AUTO, ELEMENTS<br />
Meetings.meetinguser is an ID number that also appears in the User table.
User.UserName is the name that coresponds to that ID.
At the moment this returns:
UserID CompanyName Date
I would liek it to return:
UserName CompanyName Date.
I was not sure about using more than one join in a single query...
Can any one, please, help ?
Thanks
-- modified at 5:07 Thursday 20th July, 2006
|
|
|
|
|
SELECT User.UserName, Companies.name, Meetings.meetingdate
FROM Companies, Meetings, User
WHERE Companies.id = Meetings.company AND User.id = Meetings.meetinguser
AND Meetings.complete = 0
ought to do it.
Steve S
Developer for hire
|
|
|
|
|
Simply add
INNER JOIN User
ON MeetingsUser.UserID = User.UserID after the existing ON clause and before the WHERE clause. You can have as many join clauses as you need.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Brilliant thanks guys 
|
|
|
|
|
Anyone know how to set the lenght of the date???
Like
I have a product information here that i bought it from 1st Jan 2003, then the access will automatic set it warranty valid until 1st Jan 2004 if the user select 1 year warranty, if 2 year then 2005.
how do i set those queries??? anyone try b4?
i try to set the "actual date +365" for 1 year but it will come out as number , not the date.
|
|
|
|
|
campbells wrote: I have a product information here that i bought it from 1st Jan 2003, then the access will automatic set it warranty valid until 1st Jan 2004 if the user select 1 year warranty, if 2 year then 2005.
how do i set those queries??? anyone try b4?
i try to set the "actual date +365" for 1 year but it will come out as number , not the date.
Read up on the DateAdd function.
DateAdd[^]
exp DateAdd("yyyy",1,Date())
I'd love to help, but unfortunatley I have prior commitments monitoring the length of my grass. :Andrew Bleakley:
|
|
|
|
|
i need to copy data from table to another table do i used this statement
(
insert into table1
select category_id ,category_name,category_description
from categories
)
this error appear
(
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
)
what is the soulation
ma_refay
|
|
|
|
|
Or your select statement is out of order with the column names. Depending on the difference you will have to case fields in the select statement to match the column definitions in the result table.
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|
|
Hi,
This problem occurs when you trying to insert to field a string that exceeds fields length. The only solution I could find was to set a bigger field length.
Check the length of the datatype of table in which you are inserting...
May be the length of the data type from the table whose contents you are inserting is greater than that of the table in which insertion is made.
Try out...
Good Luck!
|
|
|
|
|
Is there a way to have the progress bar increment for each record that will be imported?
When I d0 a select * (or whatever), does it bring the records in one-by-one where a progress bar can be set up?
Thanks,
RABB17
|
|
|
|
|
Rabbit17 wrote: When I d0 a select * (or whatever), does it bring the records in one-by-one where a progress bar can be set up?
No, I would use some type of animated gif (similar to the one that windows uses when empting the “recycle bin”) display it while your importing upon completion hide it.
I'd love to help, but unfortunatley I have prior commitments monitoring the length of my grass. :Andrew Bleakley:
|
|
|
|
|
I was hoping for something a little more accurate. Anyone have any ideas?
|
|
|
|
|
Rabbit17 wrote: I was hoping for something a little more accurate.
I have no idea what language you’re using; MySQL is a database server not a programming language. What was so inaccurate about my suggestion? 
Rabbit17 wrote: Anyone have any ideas?
Sure I have lots of ideas, a few even pertaining to the topic at hand. 
When selecting information from a database, you don’t know how many rows will be returned, which for all practical purposes you will have no idea how long it’s going to take that operation to complete.
I have used things like the gears turning, progress bar (that bounces back and forth quickly) to the jack in box they all work on the same principal. Start playing your animation before beginning the complex operation and hide it when finished.
If you peruse through this site there are is a whole host of different animations to choose from.
vbaccelerator[^]
I'd love to help, but unfortunatley I have prior commitments monitoring the length of my grass. :Andrew Bleakley:
|
|
|
|
|
i have sql server database
i need to copy data from existing table to an existing table
the select into statement create another new table i want to copy the datet from table to table that already exists in the database
|
|
|
|
|
INSERT INTO Foo
SELECT FIELD1, FIELD2, FIELD2 FROM bar
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|
|
i have sql server database
i need sql statement to copy all data from one table to another table
|
|
|
|
|
SELECT FIELD1, FIELD2, FIELD2
FROM table1
INTO table2
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|
|
I keep getting an exception when I call the DataAdapter Update method.
I have been trying to figure out what is causing the null reference
exception for this code for what seems like forever:
private void DoInserts(OdbcDataAdapter odbcDataAdapter, string
tableName)
{
DataTable dataTableChanged =
dsTapes.Tables[tableName].GetChanges(DataRowState.Added);
if ((dataTableChanged != null) &&
(dataTableChanged.Rows.Count > 0))
{
// Open the connection if its not already open.
if (odbcConnection.State !=
System.Data.ConnectionState.Open)
{
odbcConnection.Open();
}
//Create a new transaction.
odbcDataAdapter.InsertCommand.Transaction =
odbcConnection.BeginTransaction();
try
{
//Submit the changes.
odbcDataAdapter.Update(dsTapes,
dataTableChanged.TableName.ToString());
//Commit the changes and close the connection.
odbcDataAdapter.InsertCommand.Transaction.Commit();
}
catch (Exception ex)
{
odbcDataAdapter.InsertCommand.Transaction.Rollback();
throw (ex);
}
}
}
The exception information has not been helpful. All it says is: "Object
reference not set to an instance of an object."
I don't know if this will help, but the Insert command looks like this:
tapeLogInsert = odbcConnection.CreateCommand();
tapeLogInsert.CommandText =
"INSERT INTO " + tapeLogODBCName.Trim()
+ " (NBR, Backup_Name, BakDate, ScrDate, Location, "
+ "Tape_No, Tape_Set, Usage, Comment, UseDate) "
+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
tapeLogInsert.Parameters.Add(new OdbcParameter("NBR",
OdbcType.Int));
tapeLogInsert.Parameters["NBR"].SourceColumn = "nbr";
tapeLogInsert.Parameters.Add("Backup_Name", OdbcType.Char, 17,
"backup_name");
tapeLogInsert.Parameters.Add(new OdbcParameter("BakDate",
OdbcType.DateTime));
tapeLogInsert.Parameters["BakDate"].SourceColumn = "bakdate";
tapeLogInsert.Parameters.Add(new OdbcParameter("ScrDate",
OdbcType.DateTime));
tapeLogInsert.Parameters["ScrDate"].SourceColumn = "scrdate";
tapeLogInsert.Parameters.Add("Location", OdbcType.Char, 1,
"location");
tapeLogInsert.Parameters.Add(new OdbcParameter("Tape_No",
OdbcType.SmallInt));
tapeLogInsert.Parameters["Tape_No"].SourceColumn = "tape_no";
tapeLogInsert.Parameters.Add(new OdbcParameter("Tape_Set",
OdbcType.SmallInt));
tapeLogInsert.Parameters["Tape_Set"].SourceColumn = "tape_set";
tapeLogInsert.Parameters.Add(new OdbcParameter("Usage",
OdbcType.SmallInt));
tapeLogInsert.Parameters["Usage"].SourceColumn = "usage";
tapeLogInsert.Parameters.Add("Comment", OdbcType.VarChar, 50,
"comment");
tapeLogInsert.Parameters.Add(new OdbcParameter("UseDate",
OdbcType.DateTime));
tapeLogInsert.Parameters["UseDate"].SourceColumn = "usedate";
tapeLogAdapter.InsertCommand = tapeLogInsert;
Does any one have any idea what is going on here or how to go about
finding out? Any suggestions would be appreciated: I don't even know
how to figure out which reference is null!
|
|
|
|
|
When using c#, I've found I have to re-reference the commands when using CreateCommand.
Try:
odbcCommand tapeLogInsert = odbcConnection.CreateCommand();
instead of:
tapeLogInsert = odbcConnection.CreateCommand();
even if you lead with:
odbcCommand tapeLogInsert = new odbcCommand();
tapeLogInsert = odbcConnection.CreateCommand();
Hope that helps
RABB17
|
|
|
|
|
I am tring to create a table in a mdb file, but the maximum number of columns i can add is 40. When i try to add the same number (or move) of columns from the access interface, there is no problem.
Can you tell me if it is something i overlooked.
The connection string to the mdb file is : Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<filepath>
I am using C#, on .NET Framework 1.1 and the connection class is OleDbConnection
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
