|
You can even do what abishek said, with a simple batch file, and a scheduled task.
My advice is free, and you may get what you paid for.
|
|
|
|
|
Hi Experts,
We have created an application in ASP.Net 2.0 and with MySQL 5.0.67 as Backend. The application was running great from months, but recently we have discovered that there is a performance degrades in the database. Actually database was designed by one of my colleague how didn’t have much of database background so database design is not a professional one. I would also like to mention that this is my first Project on MySQL. I have worked on SQL Server till now. When I started analyzing the database I found indexing was missing in the tables. Introduction of Indexes has improved the performance somewhat. Can any one please tell me what else can I do to improve the performance.
Please help me its urgent
Thanks and Regards,
Paramhans Dubey
|
|
|
|
|
Paramhans Dubey wrote: didn’t have much of database background so database design is not a professional one
So the design may be wrong to start with
Paramhans Dubey wrote: indexing was missing in the tables
Another sign that he didn't know ehat he was doing
Paramhans Dubey wrote: Please help me its urgent
Do what you should have done in the first place, employ someone who knows what they are doing. Without the schema, useage, etc its impossible to say what should be done anyway.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Hi
Thanks for your reply and suggestion. We are in process to recruitin someone who knows the job, but meanwhile we just cant sit and wait for him to join and do the job. Anyways I was going through one article where I came to know about Table Engines and their usage. I also came to know there Using MyISAM table engine can improve the read perfomance of the tables but the author also wrote that Your tables will get corrupted eventually but there was no explaination about how and why it happens?
I fyou know the reason then kindly tell me
Thanks and Regards,
Paramhans Dubey.
|
|
|
|
|
OK, so your DB started performing poorly after it ran for awhile....that's a pretty broad problem, but here's a few things you might try:
1) I fully agree with Bob's post - if you want it to work correctly, get someone who knows what they're doing to set things up properly. (Though I understand that doesn't fix your problem today.)
2) Check for any tables that have grown excessively large. It's possible that you have some large logging / history tables and such that can easily be archived and deleted from the working tables.
3) If there's any way to narrow your problem down to a few areas (perhaps the top 2 or 3 stored procedures you're having trouble with?) you can look at modifying indexes specifically to help with those queries - just be sure you don't replace one problem with another.
4) You could always throw more hardware at it for a temporary fix...
If you have any more specifics about what the problem is, perhaps we could better help.
-Dave
|
|
|
|
|
Here's the short question - how can I make this work?
create function callme()
returns TABLE AS
RETURN(exec MyExistingSP)
And the longer question on why I'd want it to...
* I've got several stored procs that return table result sets that are fairly large (or larger than I care to scroll through)
* Often, I'm doing ad-hoc queries where I only want to see one or two rows returned by the stored proc.
* If I need to whip up a function/stored proc to allow this, it would be handy if it would take the stored proc name as a parameter
* Would also be nice to take a subset of the column list in a select, or add an order by
* I'm most interested in SQL Server 2000+
* Performance is not an issue (within some reason...), as this is only for typed in ad-hoc queries
select column1, column2
from callme('MySP')
where id > 500
order by LastModified --> would be nice to see the subset...
I can do this easily with a multiline function for each stored proc, but it's not worth that much work. I keep hoping there's just a twist of syntax I'm missing, but I'm afraid that's not the case...
Any thoughts?
Thanks,
Dave
|
|
|
|
|
And one other gotcha....OPENROWSET() isn't enabled on some servers I have to work with...
-Dave
|
|
|
|
|
Dave2909 wrote: create function callme()
returns TABLE AS
RETURN(exec MyExistingSP)
You can't do this -
1. You can't call a proc from a function
2. You can't pipe the output from a proc into a table
Regarding only getting the first few rows, try
SET ROWCOUNT 10
exec sproc
SET ROWCOUNT 0
This will just return the 1st 10 rows.
As for the rest of it, the only way to do it would be some pretty complex dynamic sql.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Thanks, Bob...just wish there was a different answer.
The more I think about this though, it just seems that there really should be some way to do a sub-query against a stored proc's result set from SQL Server management studio without having to define specific tables, or do anything funky like parsing the SP to auto-create a result table. Perhaps I just need to add this into a feature request for MS.
Just in case...anyone else have a suggestion on how to narrow down the results of a stored proc from within Management studio? Something like being able to run a query against the results window sure would be handy.
Thanks,
Dave
|
|
|
|
|
I got the SQL Azure Service up and running. It is running of the hosted server
MyCloud Service
While working on this app, I found some interesting things. Here is a short summary
1. select * from sys.objects IS YOUR NEW OBJECT BROWSER
2. truncate table tablename will not work in SQL Azure - yes,drop and delete does work.
3. select @@servername will not work but select @@version does work
4. DO NOT TRY exec sp_help and exec sp_who – it will not work
5.Insert WILL NOT WORK if you forget to add a primary key on your table - For example
create table test
(
my_id int,
my_name varchar(10)
)
insert into test values (1,'abhi')
and you will get an error –“ Heaps can not be replicated tables. Please create a clustered index for the table.” . Just add a PK on my_id and things will work as expected
6. Migrating data from your local DB to the cloud is not easy - check out http://www.stephenforte.net
7. Copy connection string from your https://sql.azure.com/ServerInfo.aspx page - this is the easiest and fastest way to connect your app with SQL Azure
abhi
Zimbatech Solutions
|
|
|
|
|
How we can handle mails using trigger if exchange server is down. i Am sending mail on insert of any record in table through trigger. But if exchange server is down how we can handle mails.
|
|
|
|
|
We solved this problem, by sending emails through an application.
The application will try to send the email as usual, but in case the exchange server is down, or the email can not be sent for some other reason, all relevant data (addresses, message body, etc.) is stored into a database table instead.
Another application then checks the table periodically, and will attempt to send any message there. If sending is successful the app will flag the message as sent.
My advice is free, and you may get what you paid for.
|
|
|
|
|
thanks bro but i dont want use another application to send mails. I want to send mails through triggers.
I know we can use web service to send mail. But i m tring to send mails through triggers.
|
|
|
|
|
The core problem is, what happens when your trigger is unable to send a message, right?
The question is what do you want to happen? Should your solution use an alternative medium (for example POP)? But what if the connection between the database server and the rest of the network is broken? Then you won't be able to access any alternative at all.
From your original message I understand that you are already sending emails through triggers.
Why not use our approach, but adapted to work with triggers?
Trigger --> if sending message fails --> message to table
SQL job --> check table every minute or so --> send message using same code you are using in your triggers
Message sent successful --> flag message in table as sent or delete from table.
My advice is free, and you may get what you paid for.
|
|
|
|
|
thanks for ur response i m agree with ur answer. i think taking flag is better option.
|
|
|
|
|
Your welcome, however please vote "5" or "good answer" next time. You have marked my answer as bad.
My advice is free, and you may get what you paid for.
|
|
|
|
|
ohh sorry i dont know...i don't do it intensionally
|
|
|
|
|
Hi
I'm using the in query like
select * from table where col in ('1,2,3,4')
that is a simple query but i'm here in norway where they use (,) as a decimal point and (.) as comma. They when I use the query like this
select * from table where col in ('1.2')
it works fine but when I use query like this
select * from table where col in ('1.2.3.4.5')
It gives an error that error converting datatype......
What is the solution of tis hell.
Thanks
Syed Shahid Hussain
|
|
|
|
|
The solution is to use the decimal separator as a decimal separator, and not as a separator for arguments.
select * from table where col in ('1,2,3,4')
Now, if you want to use four decimals in that place, you'd use the English notation;
select * from table where col in ('1.2,2.9,3.83,4.0')
It's a good idea to keep the database culture-agnostic, and to format the decimal just before displaying. That means that each number is treated internally like we're in England, and that everything that gets displayed is formatted from that generic English form into the current culture.
I are Troll
|
|
|
|
|
Thanks Eddy
But my problem is some thing different
the col field is nvarchar and now i want to use it in IN query. There is no floating point here. then what will be the query it all values in the column are in integers. How can i put them in IN query.
Any idea about that.
Thanks
Syed Shahid Hussain
|
|
|
|
|
Syed Shahid Hussain wrote: How can i put them in IN query.
My apologies, there was another error that I missed. The arguments after the IN operator are summed within a varchar (a string), and SQL expects a list of items, not a string. It tries to convert the entire string to the datatype of "col".
This code should run fine, even when the locale is Norsk;
SET LANGUAGE 'Norwegian'
DECLARE @myTable AS TABLE ( id INT)
INSERT INTO @myTable VALUES(1)
INSERT INTO @myTable VALUES(2)
INSERT INTO @myTable VALUES(3)
INSERT INTO @myTable VALUES(4)
SELECT * FROM @myTable WHERE id IN (1,3) As you can see, the arguments in the select -list are separated by comma's (allowed, since they're not decimals) and there are no quotes (indicating multiple values to check against, as opposed to a single varchar value)
That would translate to your query like this;
select * from table where col in (1, 2, 3, 4)
Or for decimals;
select * from table where col in (1.2, 3.89)
Make sure that the datatype of col matches the datatype of the values that you put after the IN operator
I are Troll
|
|
|
|
|
Thanks a lot
its for you
Syed Shahid Hussain
|
|
|
|
|
Thanks
|
|
|
|
|
Bah...at first glance I thought this was unanswered....in any case, drop the '' inside your IN and give it a list of separate values - sounds like that's already fixed ya right up...
-Dave
|
|
|
|
|
Hi All,
I have developed an application using C# WinApp VS 2005 and MYSQL 5.0.1. My Query is it
possible by any means that I can create a setup application that can identify and install MySQL
Server on the deployment machine if its not installed on it. Also it will run the script file to create
database and user into MySQL Server. I have done some googleing but was unable to find
appropriate answer. Can it be done using InstallShield? If so, How to do it?
Its very urgent.
Please Help me.
Thanx in Advance,
Regards,
Paramhans Dubey
|
|
|
|