"@app.route('/search', methods=['GET']) def doSearch(): #bid=request.form['fbid']…
- User
"@app.route('/search', methods=['GET']) def doSearch(): #bid=request.form['fbid'] search_term=request.args.get('q')
cursor=connection.cursor() cursor.execute("SELECT name FROM sqlite_schema WHERE type ='table' AND name NOT LIKE 'sqlite_%'") rows = cursor.fetchall() boards = [] for row in rows: json_like_memoryview = row[0] json_like_bytes = bytes(json_like_memoryview) # Convert memoryview to bytes json_like_string = json_like_bytes.decode('utf-8') # Convert bytes to str boards.append(json_like_string) query1="" for a in range(0,len(boards)): if a==0 and boards[a]!='fadenWache': query1+=f'SELECT "voll","zeit" FROM "{boards[a]}" ' elif boards[a]!='fadenWache': query1+=f'UNION SELECT "voll","zeit" FROM "{boards[a]}" ' if a==(len(boards)-1): query1+=f'ORDER BY "zeit" DESC LIMIT 1000' #print(query1) cursor.execute(query1) rows = cursor.fetchall() results = 0 threadsList={} matching_rows = [] for row in rows: json_like_memoryview = row[0] json_like_bytes = bytes(json_like_memoryview) # Convert memoryview to bytes json_like_string = json_like_bytes.decode('utf-8') # Convert bytes to str try: threadsList[results]=json.loads(json_like_string) except Exception as e: continue results+=1 #threadsList = sorted(threadsList, key=lambda k: threadsList[k]['message'], reverse=True) search_term=" ".join(search_term.split()) if not search_term: search_term=' ' response=grab_search.searchTables(session,search_term,threadsList) return response"Can you improve the performance of this code?
- Assistant
Certainly! Improving the performance of your code involves several steps, including optimizing database queries, reducing unnecessary data processing, and leveraging more efficient data structures. Here's how you can enhance the performance of your
doSearchfunction:-
Optimize Database Queries:
- Use
UNION ALLInstead ofUNION: TheUNIONoperator removes duplicate rows by default, which adds overhead. If you don't need to eliminate duplicates, useUNION ALLfor better performance. - Leverage SQLite's JSON Functions (if available): If you're using SQLite version 3.38 or later, you can use JSON functions to query JSON data directly in SQL. This reduces the amount of data transferred to your application and shifts filtering work to the database.
- Avoid Fetching Unnecessary Data: Limit the number of rows retrieved by applying appropriate
WHEREclauses in your SQL query.
- Use
-
Simplify Data Processing in Python:
- Avoid Unnecessary Data Conversion: If the data returned from the database is already in the desired format, avoid extra steps like converting between
bytes,memoryview, andstr. - Use Lists Instead of Dictionaries: If you're using integer keys starting from zero, a list is more efficient than a dictionary.
- Process Data as You Fetch It: Use generators or process rows in batches to avoid loading all data into memory at once.
- Avoid Unnecessary Data Conversion: If the data returned from the database is already in the desired format, avoid extra steps like converting between
-
Implement Full-Text Search (FTS):
- Use SQLite's FTS5 Extension: This enables efficient full-text searches on your data.
- Create FTS Virtual Tables: Mirror your data in FTS virtual tables for faster text searching.
-
Refactor Data Schema:
- Consolidate Tables: If possible, combine the multiple tables into a single table with an additional column to distinguish between boards. This simplifies queries and can significantly improve performance.
Below is the refactored code incorporating these improvements:
from flask import request, jsonify import json import re @app.route('/search', methods=['GET']) def doSearch(): search_term = request.args.get('q', '').strip() if not search_term: search_term = ' ' cursor = connection.cursor() # Get all table names except 'sqlite_%' and 'fadenWache' cursor.execute(""" SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' AND name != 'fadenWache' """) boards = [row[0] for row in cursor.fetchall()] if not boards: return jsonify([]) # Build the combined query using UNION ALL queries = [] params = [] for board in boards: # Using placeholders to prevent SQL injection queries.append(f'SELECT voll, zeit FROM "{board}" WHERE zeit IS NOT NULL') combined_query = ' UNION ALL '.join(queries) + ' ORDER BY zeit DESC LIMIT 1000' cursor.execute(combined_query) rows = cursor.fetchall() threads_list = [] pattern = re.compile(re.escape(search_term), re.IGNORECASE) for row in rows: voll_json = row[0] try: thread = json.loads(voll_json) message = thread.get('message', '') if pattern.search(message): threads_list.append(thread) except json.JSONDecodeError: continue return jsonify(threads_list)Explanation of Changes:
-
Database Query Optimization:
- Replaced
UNIONwithUNION ALLto improve query performance. - Added a basic
WHEREclause (zeit IS NOT NULL) to ensure only valid rows are processed. - Removed the complex string concatenation for building the SQL query.
- Replaced
-
Simplified Data Processing:
- Directly fetched table names into the
boardslist without unnecessary conversions. - Used a list (
threads_list) instead of a dictionary (threadsList) for storing threads. - Compiled the search pattern once using
re.compileoutside the loop for efficiency. - Performed the message search within the loop to filter relevant threads.
- Directly fetched table names into the
Additional Recommendations:
-
Use SQLite JSON Functions (if available):
If your SQLite version supports JSON functions, you can offload the search to the database:
SELECT voll, zeit FROM "{board}" WHERE JSON_EXTRACT(voll, '$.message') LIKE ?Modify the Python code accordingly:
queries = [] params = [] for board in boards: queries.append(f'SELECT voll, zeit FROM "{board}" WHERE JSON_EXTRACT(voll, '$.message') LIKE ?') params.append(f'%{search_term}%') combined_query = ' UNION ALL '.join(queries) + ' ORDER BY zeit DESC LIMIT 1000' cursor.execute(combined_query, params * len(boards))This method shifts the filtering to the database, significantly reducing the amount of data transferred and processed in Python.
-
Implement Full-Text Search (FTS):
For the best performance in text searching, consider using SQLite's FTS5 extension:
-
Create FTS Virtual Tables: Mirror your data in FTS tables.
CREATE VIRTUAL TABLE message_index USING fts5(message, content='your_table', content_rowid='id'); -
Update FTS Tables on Data Change: Ensure that any inserts, updates, or deletes are reflected in the FTS table.
-
Modify Your Query:
SELECT voll, zeit FROM message_index WHERE message_index MATCH ? ORDER BY zeit DESC LIMIT 1000 -
Adjust Python Code:
cursor.execute("SELECT voll, zeit FROM message_index WHERE message_index MATCH ? ORDER BY zeit DESC LIMIT 1000", (search_term,))
This approach is highly efficient for full-text searches over large datasets.
-
-
Consolidate Tables:
If feasible, consolidate your multiple tables into a single table with an additional column (e.g.,
board_name) to indicate the board. This simplifies querying and indexing.CREATE TABLE combined_table ( id INTEGER PRIMARY KEY, board_name TEXT, voll TEXT, zeit TIMESTAMP );Then, adjust your Python code to query from
combined_table:cursor.execute(""" SELECT voll, zeit FROM combined_table WHERE JSON_EXTRACT(voll, '$.message') LIKE ? ORDER BY zeit DESC LIMIT 1000 """, (f'%{search_term}%',))
Conclusion:
By optimizing your database queries, simplifying data processing in Python, and leveraging SQLite features like JSON functions or full-text search, you can significantly improve the performance of your
doSearchfunction. Remember to test these changes thoroughly to ensure they work with your specific dataset and environment. -