Are Stored Procedures more efficient, in general, than inlinestatements on modern RDBMS's [แปล]

**Please Un-Learn Before Read
Are stored procedures faster for simple queries [closed]
Question:
ถ้าใช้ execute stored procedure ที่มี Query ทั่วไป อาทิเช่น

SELECT ColA, ColB FROM MyTable WHERE ID = 123;

SELECT * FROM MyTable,OtherTable WHERE MyTable.ID=OtherTable.ID ORDER BY CreatedAt desc

ถ้าทำการแปลง Query ดังกล่าวเป็น Stored procedure แล้วเรียกใช้งานจะมีประโยชน์อะไรบ้าง? มีใครที่พอจะแนะนำมีข้อมูลหรือคำแนะนำอะไรที่จะช่วยให้เข้าใจได้บ้าง?

Answer2:
เหตุผลที่ควรใช้ Stored Procedure

• ลด Network Traffice คุณต้องส่ง SQL statement ผ่าน network แต่ถ้าใช่ Stored Procedure ที่ส่งไปจะถูกเรียกให้ทำงานใน batches
• Cashing query plan เมื่อ Stored Procedure ถูกเรียกให้ทำงานครั้งแรก SQL Server จะทำการสร้าง Execution plan แล้วcacheเก็บไว้ใช้งานเมื่อเรียกใช้งานอีกครั้ง เรียกยิ่งบ่อยยิ่งเร็ว
• สามารถใช้ Output parameters ถ้าใช้ SQL statement จะสามารถมี Return ได้แค่อย่างเดียวคือ Row/Record Set แต่ Stored procedure มี parameter output
• Permissions เมื่อส่ง SQL ผ่าน Statement จะต้องทำการ Grant permissions ที่ table เท่านั้น แต่ถ้าเรียกผ่าน Stored procedure สามารถกำหนดได้ที่ Stored procedure
• Separation of logic ป้องกันการ Auto Generating SQL(เขาเขียนว่า Code) และแยกไปไว้ใน Stored Procedure
• สามารถแก้ไข Application ได้โดยไม่ต้อง Recompile Application ใหม่ คุณสามารถแก้ไข Stored procedure ได้โดยไม่ต้อง Compile Code ใหม่
• Find where a table is used ถ้าอยากรู้ว่า Table ใดที่ใช้งานหรือไม่ใช้งานบ้าง ให้ทำการ export SQLใน Stored procedure ทั้งหมดออกมาแล้ว Search หาในนั้นดูว่ามีอยู่เหรือเปล่า
• Optimization ง่ายกับการที่ DBA จะทำการ Optimize SQL ใน Stored procedure และดูว่าควรทำ Index ใน Database Table ตรงไหน
• SQL injection attacks แม้เขียน SQL แบบ In-Line สามารถป้องกันการ SQL injection ได้ แต่ Stored procedure ป้องกันได้ดีกว่า

มีคนที่ถามคลีายๆกันใน Why execute stored procedures is faster than SQL query from a script? ซืึ่งเมื่อตามไปดูผมจึงได้คำตอบดังนี้ (คนที่ตอบตาม List ข้างบนตอบอยู่ในนั้นด้วย)

คำตอบที่น่าสนใจอยู่ที่คำตอบของ matt rogish :

• Perpared Statement: Store Proc จะถูก compile ไว้ก่อนล่วงหน้าก่อนที่จะถูกสั่งให้ Execute เขาเปรียบเที่ยบกับการ ทำงานของโปรแกรมที่ทำงานแบบ complie ไว้ก่อนค่อย Run กับ โปรแกรมที่เป็นแบบ Interpleter ว่าแบบไหนที่ควรจะเร็วกว่ากัน แน่ละแบบที่ Compile ไว้ก่อนย่อมเร็วกว่า แต่ด้วย CPU และ ความสามารถของ Computer ที่มีอยู่ในปัจจุบันแล้วทั้งสองอย่างแทบจะไม่มีความแตกต่างกันเลยถ้า Command นั้นไม่มีขนาดที่ใหญ่มากจริงๆ หรือ Run Command เดิมๆเป็นล้านๆครั้งในไม่่กี่วินาที

• Pre-generated query execution plan: การที่จะดึงข้อมูลออกจาก DBMS เมื่อต้องทำการดึงข้อมูลจากหลายๆTable จะมีการ"Join" ซึ่งกระบวนการทั่วไปของ DBMS คือจะต้องนำเอา Query ไปประเมินเพื่อทำการสร้าง Execution Plan ก่อนที่จะทำงาน เพื่อเป็นการ Optimize Resuorce ที่จะใช้งานจริง แต่ในความเป็นจริงๆ ปัจจุบัน DBMS แทบจะทุกตัวมีความสามารถในการ Cache Plan ของ Query กับ Store Proc ได้เหมือนๆกันอยู่แล้ว

• Reduced network latency A) ลดจำนวนความยาวของ Command ที่ถูกส่งไปแต่ละรอบได้จาก select .... from ... เป็น exec seleData B) ส่งชุดของ Data ต้องการทำงานไปให้ Database แค่รอบเดียว แล้วเขียน Store Proc เพื่อทำงานโดยไม่ต้องส่งไปส่งกลับเรื่อยๆ ความเร็วปัจจุบันของ ethernet คือ 1GB (up to 10 GB) จึงการรับส่งข้อมูลขนาดไม่ถึงหนึ่ง Mega Bytes

• Potential cache benefits ถ้าโปรแกรมที่ทำงานอยุ่เป็นเครื่องเดียวกับ Database จะสามารถใช้งาน Data ชุดนั้นได้ทันที จะทำได้ก็ต่อเมื่อโปแกรมของคุณกับDBMSอยู่บนเครื่องเดียวกันและสามารถ Share Memory กันได้

• Parameterized / Prepared SQL การ Query ข้อมูลโดยใช้คำสั่ง Select ... from ... where id = ? and name = ? ดูเหมือนว่าจะทำให้ Store Proc ได้เปรียบกว่า แต่ในความเป็นจริงแล้ว ไม่ว่าจะเป็นทั้งส่งแบบ Ad hoc SQL หรือแบบผ่าน Store Proc DBMS ก็สามารถทำงานทั้งสองแบบได้เหมือนกัน

        สุดท้ายเขากล่าวไว้ว่า วันหนึ่ง Store Proc ก็จะทำงานไม่ต่างกันกับ Ad hoc SQL อยู่ดี การทีเอา SQL Statement เล็กๆน้อยๆไปใส่ไว้ใน Store Proc เพียงเพราะคำกล่าวว่า Store Proc ทำงานได้เร็วกว่านั้นดูจะขัดๆเขิลๆไปหน่อย


ผม(giornoadd) ไม่ใช่แฟนพันธุ์แท้ของ Store Proc แน่ๆ ประสบการณ์ที่ผมเขียนโปรแกรมมาพบว่าเมื่อไรก็ตามที่เริ่มมีการเอา Business logic เล็กๆน้อยๆไปใส่ไว้ใน Store Proc ก็จะมี logic ใหญ่ๆตามมาเสมอสิ่งสำคัญคือมันเสี่ยงต่อการเป็น Vendor Login, Legacy system, Automate Test ยากและปัญหาอื่นๆอีกมากมาย ดังนั้นถามเป็นไปได้วิธีที่จะทำงานกับระบบที่ต้องใช้งาน Store Proc คือเลิกใช้มันซะ หรือถ้าจำเป็นต้องใช้จริงๆให้สร้าง Layer ของ application ขึ้นมาโดยเฉพาะเพื่อใช้งาน Store Proc โดยเฉพาะ แยกให้ห่างจาก Application ให้มากที่สุดเท่าที่จะมากได้ และใช้งานให้น้อยที่สุดเท่าที่จะเป็นไปได้





ref: Are stored procedures faster for simple queries [closed]
Are Stored Procedures more efficient, in general, than inline statements on modern RDBMS's?
Why execute stored procedures is faster than SQL query from a script?