|
My guess is that it is not really the number of records that is the issue, but rather the way in which you are doing it. Frequently queries can be written in many-many different ways, and still accomplish the same end result. However, what's going on inside the db-engine may not be at all the same.
I had a query (seemed perfectly fine to me) that was taking about 8-seconds to run. This was WAY too slow as it was going to be used several million times. Everything looked fine to me and I couldn't figure why it was running so slow. I began experimenting, pulling things out, rearranging, and suddenly the query began running in microseconds instead of seconds. Something in the design of the original query was causing the engine to do LOTS of churning internally. I never did really understand what the problem was, but didn't care since I got it fixed.
Access doesn't have a profiler (that I'm aware of). The larger systems will actually SHOW you how the query is being compiled, and the steps that will be taken to accomplish it. You can use that to rearrange things.
Without knowing the text of your query, and what you're trying to accomplish, I doubt anyone here is going to be able to give you any specific help. The only general advice I can give is to use the built-in query wizards. They will often show you how to do things you might not have thought of on your own. They write queries that Access recognizes how to optimize.
|
|
|
|
|
I have created a DTS to load the data from csv file in to a table in the database. Upto this its working fine. I did it with automapping column.
But now, I want to check for the duplicate entry of the Bank Recipt column(which is not a primary key column), if no entry found then it will upload the file data in to the table other wise it will give some error message.
And that error message I would like to store in table in the same database and show it to the user.
If anybody have idea about it, please give me advice or code snippet if possible. Thank you.
Regards,
Aleem.
S/W Engineer
Akebono Soft Technologies
aleem_abdul@akebonosoft.com.
|
|
|
|
|
what is SqlDataAdapter and SqlDataReader? and what is different between SqlDataAdapter and SqlDataReader?.these 2 r different from dataadapter and datareader.
|
|
|
|
|
|
Hi Friend'z...
I want to enter a row data to the sql server table, from the data entered by the user in the windows form.
I hav a query
string str="insert into books values ("+'@textBox1.Text'+","+'@textBox2.Text'+")";
what is wrong in this? pl help me in doing....
thanks in advance
 
|
|
|
|
|
Write down the query with actual values...Best way is to run in debug mode and then see wat is the actual query in str variable.
Mubashir
Every job is a self portrait of the person who did it.
|
|
|
|
|
hope the two values you are trying to insert are strings
then the statement should be ...
string str = "INSERT INTO books VALUES ('" + textBox1.text + "', '" + textBox2.text + "')"
Regards
KP
|
|
|
|
|
Try
SQLstr="insert into books ({col1}, {col2}) values ('" & textBox1.Text & "', '" & textBox2.Text & "')";
(This is VB ... I'm not sure what language you're using ... the important thing is the {col1} and {col2} which are the names of the table colums that @textBox1 and @textBox2 are going to be inserted into)
I find that a good debug tip is to use Query Analyser to test the structure of my SQL statement before I insert it into code.
So I would try something like INSERT INTO books VALUES ('SQL Programming', 'Robert Vieira') and when that didn't work I'd try INSERT INTO books (title, author) VALUES ('SQL Programming', 'Robert Vieira')
|
|
|
|
|
Hi all,
I have 4 tables where in each table 3 coulmns are the primery key(Year,Month,ProductID) and it has notherextra different columns.
Now i wanna join all four related to primery key. and combine all columns.
Table 1
Year,Month,ProductID,Location,Sales
Year,Month,ProductID,Location,Returns
Year,Month,ProductID,Location,DuePayment
Year,Month,ProductID,Location,Profit
I wanna join and get Following
Location,Sales,Returns,DuePayments,Profit
Also i wanna group the table Location
And Filter it for Month=03
Please Help me
I need the SQL Select statement
Regards
Ruwandi
rkherath
|
|
|
|
|
It should be something like:
Select t1.location, t1.sales, t2.returns, t3.duepayment, t4.profit
from table1 t1
join table2 t2 on t1.year = t2.year and t1.month = t2.month and t1.productid = t2.productid and t1.location = t2.location
join table3 t3 on t1.year = t3.year and t1.month = t3.month and t1.productid = t3.productid and t1.location = t3.location
join table4 t4 on t1.year = t4.year and t1.month = t4.month and t1.productid = t4.productid and t1.location = t4.location
where t1.month = '03'
order by t1.location
It might be a good idea for you to get a sql book so you can learn the basics of write sql queries.
Ben
|
|
|
|
|
Hi Ben,
Thx for replying. I tried on the code and it didnt work out. Actually It doesnt show error but no records shown as the resul. I checked manually and there are records that should be displayed.
I have a doubd that , is it a problem that i am using year , month, Pro ID, Location as composite key(All are primary keys in both the tables)
if the case like this is there a different way to do this.
regards
Ruwandi
rkherath
|
|
|
|
|
As per what i understood hope this is what you are looking for ....
SELECT Year, Month, ProductID, Location, SUM(Sales), SUM(Returns),
SUM(DuePayments), SUM(Profit)
FROM (
SELECT Year,Month,ProductID,Location,Sales FROM TableA
UNION
SELECT Year,Month,ProductID,Location,Returns FROM TableB
UNION
SELECT Year,Month,ProductID,Location,DuePayment FROM TableC
UNION
SELECT Year,Month,ProductID,Location,Profit FROM TableD
)a
WHERE Month = 3
GROUP BY Year, Month, ProductID, Location
Regards
KP
|
|
|
|
|
Hi all,
I have created a dts package to upload data from a csv file in to a table in SQLServer. Now I want to do 2-things.
1. I dont want the same rows to be loaded in the table more than once, means the records must be unique.
2. I would like to display an error message in to a .Net UI when DTS package fails runnig, or when the same records are tried to be loaded.
Please tell me any idea how can I achieve this, because I am new to this DTS Package services. Thank you.
Regards,
Aleem.
S/W Engineer
Akebono Soft Technologies
aleem_abdul@akebonosoft.com.
|
|
|
|
|
hello every one, my name SAN Vibol, now i just working with unicode with SQL server 2000, i can insert, show all record, but when i select (WHERE) sometime related my key word, sometime show other data. it dont follow my mine.
example: i have data ( my unicode language to english)
School
Water
We
You
Class
SQL:
select ItemName from Tbl_Test where ItemName=N'School'
Result
School
or
School
You
or Class
Water
School
================================================
i dont kown why it like this. bec' i need result school, why it show any data?
Please help me
SAN Vibol
SAN Vibol
|
|
|
|
|
i'm facing a problem connecting to mysql using C#
i'm using oledb and it gives me (MySQLProv' provider is not registered on the local machine)
i downloaded and installed ADO.NET Driver for MySQL (Connector/NET)
so does anybody know the problem
|
|
|
|
|
Hi,
You have downloaded MySQl connector, so use it instead of oledb. It's a native driver and fully managed .Net so more performant than oledb driver.
HTH.
Hayder Marzouk
|
|
|
|
|
thanks Hayder. but i think we need to add reference first to use the name space
|
|
|
|
|
Hi,
I am using C# and MSSQL to read a bunch of complex objects from a file and trying to insert them into a database. To simplify and point out my problem I'll use this structure:
Object:
Name
Array a
Table1:
ObjectID (Primary Key)
Name
Table2:
ObjectID (Foreign Key)
ArrayIndex
value of a @ ArrayIndex
What I can't figure out is how to update the database. I can load the objects into a dataset, but how do I get the primary key from Table1 to insert as a foreign key into Table2? I'm currently unable to update without that foreign key.
Thanks!
|
|
|
|
|
You can do this job
SELECT * FROM Table1
Store the result in a Combobox or DropDownList control and then use the SelectedValue of that control
|
|
|
|
|
If you have the data already then there shouldn't be an issue. If the keys don't exist yet then you would probably have to create them on the fly. I am guessing that you are perhaps using identity columns, so you don't know the key until after you have inserted. If this is what you are doing, an option is to write a stored procedure that returns the new identity in an output parameter so the next insert can use it. In the procedure after the insert you can use @@scope_Identity to get that value that you want to return. It is the identity value that was used for that insert.
Hope that helps.
Ben
|
|
|
|
|
Here is the logic I'm trying to use...and maybe I'm thinking about this all wrong.
Loop through Objects loaded from file (Loop1)
Add Object to Dataset (Table1)
Loop through Array a in Object (Loop2)
I need the key from the Object above at this point
Add values from Array a to Dataset (Table2)
Table1.Update
Table2.Update
What I was trying to avoid was updating the database each time through Loop1, in order to get back an a unique ID (or primary key), feeling all those updates may be quite a performance hit. Maybe there is no way around that though. Any thoughts?
Thanks,
Brandon
|
|
|
|
|
Can anybody please tell me the steps to include activeX scripting in a DTS package. I would be thankfull to them.
Regards,
Aleem.
S/W Engineer
Akebono Soft Technologies
aleem_abdul@akebonosoft.com.
|
|
|
|
|
So, I've got an open connection to my DB, I've got a dataSet, I've made my table and added it to the dataSet, I've got a SqlDataAdapter, constructed with a SELECT command string. I would like to use the SqlCommandBuilder to get the rest of the SQL commands in the DataAdapter, but I can't seem to find the correct order to do everything. I need to add the table to the dataset, initialize the adapter for the table, build the other commands for the adapter, and finally save everything back to the DB on disk. Below is my code, and I'm sure hoping someone can point me in the right direction. I know it seems long, but there are lots of comments and extra lines. I really need help on this, been working all hours for 2 days trying to figure this out.
<code>
private void CheckDDBTables()
{
DataTable table;
DataColumn column;
DirectoryInfo dir = new DirectoryInfo( Application.StartupPath + "\\forms\\" );
bool needNewtable = true;
CustomFormat frm = new CustomFormat();
SqlDataAdapter tempDA;
SqlCommandBuilder builder;
string selStr = ""; // SELECT "column_name" FROM "table_name"
DBConn.Open(); // a global SqlConnection, intialized with the correct connection string
// loop through all files in the directory
foreach( FileInfo fli in dir.GetFiles( "*.*" ) )
{
// for every form file, make a table, to make the DataAdapter
// load the file into the form, function also inits
LoadFile( "\\forms\\" + fli.Name, out frm );
// init the selStr
selStr = "SELECT ";
// make a new table for this file
table = new DataTable( frm.name.Substring( 0, frm.name.Length - 4 ) );
// identity index column
column = new DataColumn( "Listing", typeof( int ) );
column.AllowDBNull = false;
column.AutoIncrement = true;
column.Unique = true;
column.AutoIncrementSeed = 1000;
table.Columns.Add( column );
table.PrimaryKey = new DataColumn[] { table.Columns[ 0 ] };
// add column to selStr
selStr += column.ColumnName + ",";
// begin adding columns, for each item in frm
foreach( Item gc in frm.ItemList )
{
// new column
column = new DataColumn( gc.name, typeof( string ) );
// add the column to the table
table.Columns.Add( column );
// add column to selStr
selStr += column.ColumnName + ",";
} // end for each Item in frm.ItemList
// finish selStr
selStr = selStr.Substring( 0, selStr.Length - 1 ) + " FROM " + table.TableName;
// init the tempDA
tempDA = new SqlDataAdapter( selStr, DBConn );
// loop through all the tables in the DB
foreach( DataTable tbl in DBDataSet1.Tables )
{
if( tbl.TableName == fli.Name )
needNewtable = false;
}
// after searching the tables, if we need the table, it's made, just add it
if( needNewtable )
{
DBDataSet1.Tables.Add( table );
DBDataSet1.AcceptChanges(); // wasn't sure if this needs to be here, didn't seem to affect anything
}
// see if we can fill the dataset with anything
try
{
tempDA.Fill( DBDataSet1 );
}
catch( SqlException sqle )
{
// returning SqlException "Invalid object name '<name>'"
// table must not exist, so add it
DBDataSet1.Tables.Add( table );
}
// try to make the rest of the commands
// same error if i try to access the other commands
builder = new SqlCommandBuilder( tempDA );
// update and check below
DBDataSet1.AcceptChanges();
tempDA.Update( table );
// save in list for later
tableDA.Add( tempDA );
} // end loop through filenames
// and save the dataset
DBDataSet1.AcceptChanges();
}
</code>
A soft glow comes from the pit in the darkness.
The clicking noise become faster - and louder.
A wind begins to stir up from the pit, as the
creature flexes it's wings, preparing for flight.
You stare into the pit, and hear a voice say
in your mind, "If you survive the encounter,
declare it to the world."
The Code Demon Rises.
|
|
|
|
|
I have a problem like this:
when i restore my database backup file (ex: the file name is warehouse) , i recognized that a transaction log file is unusual (the file name after restore is: warehouse_log.LDF) because a size of this file is very big: 2.3 gigabytes.
Before i backup my database, this file (warehouse_log.LDF) is only 1 megabytes.
when i backup my database, the backup file is only 4 megabytes.
Who know what happend, please help me how to make this file to normal. Thankyou very much. Please contact with me at email:
aeriscute@yahoo.com
Hanh Aeriscute
|
|
|
|
|
your query is not clear to me. however ...
Transaction log file size can be reduced by
1. take backup of transaction log
2. shrink the transaction log
if required repeat both steps
Regards
KP
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
