This probably isn’t what you think, but I couldn’t think of an accurate title. I use and INSERT query at the start of my process then an UPDATE query later on. I need to reduce the size of my UPDATE query by stopping it from updating data I already have in my record.
When I say best, I guess I mean quickest to code, least bugs.
I know in dotnet theres datatables and functionality to create SQL statement. But I’m not very familiar with those.
So instead I could :-
A) Use my my existing table class and set the properties in a second instance of the class, just to save the data till later. I could then pass this instance into a comparison function. I think I might have to write this function myself as I’m not aware of any inbuilt dotnet property comparison functionality.
B) I could write something which analyses the actual queries and gain the data from each and make the comparison.
C) I could create an array where I have a structure with two values field name and value, then do the comparison and assignments to it.
Thoughts?
High quality fashion jewellery at a reasonable price!
by JM
SoftTester uses a program written in VB.Net to perform all of the transactions. At the moment.t this runs during the night. In the last few months is undergone some big changes moving from MS Access offline with My SQL, to completely with MySQL.
Obviously when I did this conversion I had to do it in stages and leave tweaks till last after things settled down.
The next thing I need to do is query tweaks to improve the performance of the program and the MySQL process on the web server. Then I can do more with the program and hopefully convince my ISP that the program is fast enough to run during the day OR as he prefers in realtime.
Following a conversation with my ISP about the queries the program uses, I thought i’d share the I’d share the bigger picture.
Try to avoid text searches, use number / indexes fields.
When you have to use text searches try to include another clause to cut down that amount of records that the database has to look at to provide results.
This is a problem for me as I search on pad url as it is unique. I don’t have any other data which is unique.
Next, instead of having big update queires which update fields which are already up-to-date, store the data in variables or some sort of structure and produce a smaller update query.
Then I have in my main table what I call a locking flag. This allows me to gain the key value after the record is added. Instead I can do a Max on key field plus one, then I have the record key id. Oh of course I use auto num.
Its a start but I’m hopeful these will make a big difference
Here’s The Fastest And Easiest Way To Increase MS Access Database Security
by JM
