MySQL / Database Question - STORED PROCEDURES / FASTER QUERIES?

[Superwormy]

Aight so I'm using MySQL currently, and theres one query imparticular which I run OVER AND OVER AND OVER again, and I've been told Stored Procedures are the way to go to make it faster, eliminating the query parse time.

Well, MySQL doesn't support Stored Procedures :-(

Is there another way to speed up the query in MySQL, something along the lines of Stored Procedures maybe?

The query is simple, just a simple:
SELECT * FROM tablename WHERE IDX = value

 

[glugglug]

A prepared statement will do almost the same thing, as long as you use the same prepared statement over and over rather than re-creating it for each call.

See the example in section 3.5.3.1 here

 

[manly]

Prepared statements also reduce the query processing time to a one-time cost. For simple standard SQL queries, developing a stored procedure is probably overkill anyway.

I'm not sure who started the "stored procedures for everything" is good camp, but MS seems to be one of the major proponents. SP in and of themselves do not guarantee better throughput or faster processing time.

 

[glugglug]

sps provide some important advantages besides processing speed:

1) security -- you can give permission to run the sp without giving permission to access any of the tables it looks at/modifies. This keeps all table access going through known code.

2) if everything is run through an sp, the db itself acts as a single repository of all code that is run against it, so the DBAs don't have to look all over the place to find how things are being used.

 

[Superwormy]

Bummer that the prepared statements seem to apply only to MyODBC and not to MySQL in general... anything else?

 

[bot2600]

Originally posted by: glugglug
sps provide some important advantages besides processing speed:

1) security -- you can give permission to run the sp without giving permission to access any of the tables it looks at/modifies. This keeps all table access going through known code.

2) if everything is run through an sp, the db itself acts as a single repository of all code that is run against it, so the DBAs don't have to look all over the place to find how things are being used.

unfortunantly I think that changed with sp3 for sql 2000, with a stored procedure, the query will fail if the user making the call does not have access to the table, so I guess you need to have a tier now with a component that has access making the call for a user that doesnt or something😱

 

[DT4K]

Originally posted by: bot2600

Originally posted by: glugglug
sps provide some important advantages besides processing speed:

1) security -- you can give permission to run the sp without giving permission to access any of the tables it looks at/modifies. This keeps all table access going through known code.

2) if everything is run through an sp, the db itself acts as a single repository of all code that is run against it, so the DBAs don't have to look all over the place to find how things are being used.

unfortunantly I think that changed with sp3 for sql 2000, with a stored procedure, the query will fail if the user making the call does not have access to the table, so I guess you need to have a tier now with a component that has access making the call for a user that doesnt or something😱

NO, it has not changed. Giving a user exec permission on a stored procedure is all that is needed. You do not have to give any permissions directly on the tables. This is the same in sp3 as it was in previous versions.
The exception is that if your stored proc generates dynamic sql and executes it, the permissions do not carry over from the original stored procedure to the dynamic sql.

--So if you put
declare @SQL nvarchar(100)
set @SQL = 'select * from tblMyTable'
exec(@SQL)
--into your stored procedure, the user would need both exec permission on the proc AND select permission on the table.



--But if you put
select * from tblMyTable
--in your stored procedure, the user only needs exec permission on your stored procedure and not any permission on the table.

Sorry, I don't know MySQL so can't comment on that.

 

[Shazam]

mySQL is so fast anyways that you're still way ahead of the game with mySQL's prepared statements.