# django orm take to much time in mysql and server

**URL:** <https://forum.djangoproject.com/t/django-orm-take-to-much-time-in-mysql-and-server/10473>\
**Category:** Using Django\
**Created:** [November 11, 2021, 11:56am UTC](https://forum.djangoproject.com/t/django-orm-take-to-much-time-in-mysql-and-server/10473 "2021-11-11T11:56:45Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![alizabetpoor](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/alizabetpoor/32/6334_2.png) [@alizabetpoor](https://forum.djangoproject.com/u/alizabetpoor)\
**Post date:** [November 11, 2021, 11:56am UTC](https://forum.djangoproject.com/t/django-orm-take-to-much-time-in-mysql-and-server/10473/1 "2021-11-11T11:56:45Z")

</div>

we have a script in django and this script loop on the csv rows and add csv rows to the database. when we do it in local and with sqlite script do the job so fast but when we do it in server and with mysql it take too much time.  
I debug the script and I found out after some row when we reach line of getting first object it takes to much time for response  
code:

```auto
filtered=Stock_Csv.objects.filter(ticker=name)
filtered_by_date=filtered.filter(date=date_object)
current_obj=filtered_by_date.first()

```

when reach this line: `current_obj=filtered_by_date.first()` it takes to much time for response

but it’s just happened in mysql and in local we don’t have this problem.

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [November 11, 2021, 1:21pm UTC](https://forum.djangoproject.com/t/django-orm-take-to-much-time-in-mysql-and-server/10473/2 "2021-11-11T13:21:56Z")

</div>

> [@alizabetpoor](#):
>
> ```auto
> filtered=Stock_Csv.objects.filter(ticker=name)
> filtered_by_date=filtered.filter(date=date_object)
> current_obj=filtered_by_date.first()
> 
> ```

First, keep in mind that the first two statements do _not_ actually access the database.

Querysets are lazy, which means they aren’t passed to the database server for execution until it’s necessary to do so. So it’s only the last statement that is hitting the database. The first two statements only serve to help define the query.

Now, what is meant by “too much time”?  
How large is the Stock\_Csv table (how many rows)?  
What is your primary key for the table?  
Do you have a default ordering defined on the model?  
(You’re not specifying an order\_by clause in the query, how do you know which one you’re going to get as “first”?)

It might be worth creating an index for ticker and another index for date.

You might also look at your MySQL configuration. Look at the query being generated, and run that individually, directly on the database. Then run it from your server. (What I would be trying to do at this point is isolate where the time is being spent.)

---

<div class="post-metadata">

**Author:** ![alizabetpoor](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/alizabetpoor/32/6334_2.png) [@alizabetpoor](https://forum.djangoproject.com/u/alizabetpoor)\
**Post date:** [November 11, 2021, 3:38pm UTC](https://forum.djangoproject.com/t/django-orm-take-to-much-time-in-mysql-and-server/10473/3 "2021-11-11T15:38:25Z")

</div>

When I debugging I notice that for the some first row for example 50 or 100 (for any runs was different) it’s okay and so fast but after some rows when code reach to the .first() take 2 or 3 seconds for each row to response.I don’t use order\_by because I have just one object in my filtered\_by\_date variable.  
this is my query for example:

```auto
SELECT "csvtojson_stock_csv"."id", "csvtojson_stock_csv"."ticker", "csvtojson_stock_csv"."date", "csvtojson_stock_csv"."open", "csvtojson_stock_csv"."high", "csvtojson_stock_csv"."low", "csvtojson_stock_csv"."close", "csvtojson_stock_csv"."vol", "csvtojson_stock_csv"."arzesh", "csvtojson_stock_csv"."tedad", "csvtojson_stock_csv"."diroz", "csvtojson_stock_csv"."shamsi", "csvtojson_stock_csv"."akharin", "csvtojson_stock_csv"."office" FROM "csvtojson_stock_csv" WHERE ("csvtojson_stock_csv"."ticker" = کرماشا AND "csvtojson_stock_csv"."date" = 2020-02-23)

```

and when run this query in phpmyadmin mysql I got this error:

```auto
SQL query:

SELECT "csvtojson_stock_csv"."id", "csvtojson_stock_csv"."ticker", "csvtojson_stock_csv"."date", "csvtojson_stock_csv"."open", "csvtojson_stock_csv"."high", "csvtojson_stock_csv"."low", "csvtojson_stock_csv"."close", "csvtojson_stock_csv"."vol", "csvtojson_stock_csv"."arzesh", "csvtojson_stock_csv"."tedad", "csvtojson_stock_csv"."diroz", "csvtojson_stock_csv"."shamsi", "csvtojson_stock_csv"."akharin", "csvtojson_stock_csv"."office" FROM "csvtojson_stock_csv" WHERE ("csvtojson_stock_csv"."ticker" = کرماشا AND "csvtojson_stock_csv"."date" = 2020-02-23) LIMIT 0, 25

MySQL said:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '."id", "csvtojson_stock_csv"."ticker", "csvtojson_stock_csv"."date", "csvtojs...' at line 1

```

Every csv can have 2000 rows less or more.  
Primary key in setting of django is set to django.db.models.BigAutoField  
We don’t have a default ordering in model.  
All of this problem happened in mysql not in the sqlite.

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [November 11, 2021, 4:45pm UTC](https://forum.djangoproject.com/t/django-orm-take-to-much-time-in-mysql-and-server/10473/4 "2021-11-11T16:45:45Z")

</div>

The `query` attribute generated by something like `print(queryset.query)` is _not_ valid SQL as-is. It’s not the true query being issued to the database. You need to enclose the constants in the query with single quotes.

Your `WHERE` clause should look more like this:  
`WHERE ("csvtojson_stock_csv"."ticker" = 'کرماشا' AND "csvtojson_stock_csv"."date" = '2020-02-23')`

You should define indexes in your model for both the ticker and date fields.

---

<div class="post-metadata">

**Author:** ![alizabetpoor](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/alizabetpoor/32/6334_2.png) [@alizabetpoor](https://forum.djangoproject.com/u/alizabetpoor)\
**Post date:** [November 11, 2021, 6:16pm UTC](https://forum.djangoproject.com/t/django-orm-take-to-much-time-in-mysql-and-server/10473/5 "2021-11-11T18:16:00Z")

</div>

thank you,I do Indexing and I think it’s get better but I still have hanging in my code I upload the video for you.  
link of video:[Upload files for free - execcsvtojson.mp4 - ufile.io](https://ufile.io/6sl5n1e8)  
and I change the code to this:

```auto
current_obj=Stock_Csv.objects.filter(Q(ticker__exact=split_ticker(row1[0])) & Q(date__exact=date_object)).first()

```

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [November 11, 2021, 6:21pm UTC](https://forum.djangoproject.com/t/django-orm-take-to-much-time-in-mysql-and-server/10473/6 "2021-11-11T18:21:49Z")

</div>

Ok, so there’s more going on here than just that query. It looks like you might be running this query multiple times in a loop?

Please post the complete view in which this query is executing. There may be other factors involved beyond just the query itself.

---

<div class="post-metadata">

**Author:** ![alizabetpoor](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/alizabetpoor/32/6334_2.png) [@alizabetpoor](https://forum.djangoproject.com/u/alizabetpoor)\
**Post date:** [November 11, 2021, 6:29pm UTC](https://forum.djangoproject.com/t/django-orm-take-to-much-time-in-mysql-and-server/10473/7 "2021-11-11T18:29:29Z")

</div>

yes I do it in the loop.  
I change the code a lot but this is what I ran in the video:

```auto
from csvtojson.models import Stock_Csv
import csv
from django.db.models import Q
from datetime import datetime
import os,glob
from django.db import connection
from farsi_tools import standardize_persian_text
import logging

logging.basicConfig(filename='csvtojson.log', filemode='w',format='%(asctime)s - %(message)s', level=logging.INFO)

def split_ticker(ticker):
    if "-" not in ticker:
        return ticker
    split_word=ticker.split("-")[0:-1]
    word="".join(split_word)
    return word

def add_csv_to_database():
    print("csvtojson ejra shod")
    #csv folder path
    path = r'/home/chartban/chartban/tsefile'
    extension = 'csv'
    os.chdir(path)
    #all csv file in folder
    all_csv_file = glob.glob('*.{}'.format(extension))
    for csvfile in all_csv_file:
        fullpath=f"{path}/{csvfile}"
        with open(fullpath,encoding='utf-16le',errors='ignore') as csvfile:
            try:
                csvreader = csv.reader(x.replace('\0', '') for x in csvfile)#replace null byte
                header=[]
                header=next(csvreader)
                should_update=[]
                should_create=[]
                row1_count=0
                
                for row in csvreader:
                    if row1_count==1 or len(row)==0:
                        break
                    row1=row
                    row1_count+=1
                if not row1:
                    break
                print(split_ticker(row1[0]))
                logging.info(split_ticker(row1[0]))

                for row in csvreader:
                    #agar row khali nabod
                    if len(row) !=0:
                        myrow=[]
                        column_count=1
                        for column in row:
                            if column_count == 1 or column_count == 13:
                                #split the word untill last -

                                myrow.append(split_ticker(column))
                            else:
                                myrow.append(column)
                            column_count+=1
                        date_object=datetime.strptime(myrow[1][2:],"%y%m%d")
                        # find exist object
                        print("before")
                        current_obj=Stock_Csv.objects.filter(Q(ticker__exact=standardize_persian_text(split_ticker(row1[0]))) & Q(date__exact=date_object)).first()
                        print("after")

                        # update object
                        if current_obj:

                            current_obj.open=myrow[2]
                            current_obj.high=myrow[3]
                            current_obj.low=myrow[4]
                            current_obj.close=myrow[5]
                            current_obj.vol=myrow[6]
                            current_obj.arzesh=myrow[7]
                            current_obj.tedad=myrow[8]
                            current_obj.diroz=myrow[9]
                            current_obj.shamsi=myrow[10]
                            current_obj.akharin=myrow[11]
                            current_obj.office=myrow[12]

                            should_update.append(current_obj)
                        else:
                            # make new object of Stock_Csv
                            stock=Stock_Csv(ticker=standardize_persian_text(myrow[0]),
                                            date=date_object,
                                            open=myrow[2],
                                            high=myrow[3],
                                            low=myrow[4],
                                            close=myrow[5],
                                            vol=myrow[6],
                                            arzesh=myrow[7],
                                            tedad=myrow[8],
                                            diroz=myrow[9],
                                            shamsi=myrow[10],
                                            akharin=myrow[11],
                                            office=myrow[12])

                            should_create.append(stock)
                if should_create:
                    Stock_Csv.objects.bulk_create(should_create)
                if should_update:
                    Stock_Csv.objects.bulk_update(should_update,fields=["open","high","low","close","vol","arzesh","tedad","diroz","shamsi","akharin","office",])

            except Exception as e:
                print("error!!! : ",e)
                pass

```

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [November 12, 2021, 2:22am UTC](https://forum.djangoproject.com/t/django-orm-take-to-much-time-in-mysql-and-server/10473/8 "2021-11-12T02:22:47Z")

</div>

Ok, one of the **major** issues here is that what you’re seeing is misleading you.

Python output from the print function is, by default, buffered. The pauses you are seeing are _not_ necessarily caused by a delay in that one statement, but caused by the buffer being emptied and waiting for the next buffer to be filled.

If you’re going to correctly and accurately evaluate this, you need to track the actual time spent in each operation, and not rely upon when you see the buffer being sent to the screen.

---

<div class="post-metadata">

**Author:** ![alizabetpoor](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/alizabetpoor/32/6334_2.png) [@alizabetpoor](https://forum.djangoproject.com/u/alizabetpoor)\
**Post date:** [November 12, 2021, 8:26am UTC](https://forum.djangoproject.com/t/django-orm-take-to-much-time-in-mysql-and-server/10473/9 "2021-11-12T08:26:43Z")

</div>

timing for each file in the mysql and server:  
link photo:[Pasteboard - Uploaded Image](https://pasteboard.co/zbpvlzyZ2JQt.png)  
timing for each file in the sqlite and local:  
link photo:[Pasteboard - Uploaded Image](https://pasteboard.co/ETKfviFkEyj5.png)