What Is the Affect of Convert() on Index While Searching

What is the affect of CONVERT() on INDEX while searching?

Generally, when you have a function on the left side of the comparison in a WHERE clause, the server will be unable to utilize the index on the referenced column.

In your first example, there is no comparison to the DateFrom column directly. Instead, you are using a function on the column. When the server expands this, it must perform the function on each column value, and the resulting value is not indexed. Therefore, no index can be used to improve the query.

Also, in your first example, you indicated that the DateFrom column is a datetime column. Yet, you're converting the column to a string and doing a string comparison. Hence, the server will not use your datetime index.

In your second example, you are comparing the constant value with the date column directly, so the server may utilize the index. The server will convert the string constant on the right side of the comparison into a datetime value. However, it won't use the index in all cases. For example, if you have a very few number of rows, the server may decide not to use the index and just scan the few rows.

Both queries may yield the same result set, but they are still very different.

Index not used due to type conversion?

You could use a function-based index.

Your query is:

select
    unique_key 
from
    src_table
where
    natural_key1 = :1

In your case the index isn't being used because natural_key1 is a varchar2 and :1 is a number. Oracle is converting your query to:

select
    unique_key 
from
    src_table
where
    to_number(natural_key1) = :1

So... put on an index for to_number(natural_key1):

create index ix_src_table_fnk1 on src_table(to_number(natural_key1));

Your query will now use the ix_src_table_fnk1 index.

Of course, better to get your Java programmers to do it properly in the first place.

Pandas : Apply converts index values to float while iterating

As, rafaelc mentioned:

This is an automatic conversion that pandas will do to optimize operations with row. If row contained both integers and floats, it'd need to be of dtype object, which removes most of the gain from using pandas in the first place. Now, having all floats brings a lot of performance power. Sometimes this automatic conversion is not possible, though. For example, if you had a column with string values, then there'd be no possibility other than holding row values with dtype=object, and you would see your index with ints.

Solution is,
Explicitly, do not reset_index(). Access the index with row.name instead.

How to improve performance for datetime filtering in SQL Server?

Just a suggestion when it comes to indexes on datetime in msql is the index footprint impacts search times (Yes this seems obvious...but please read onward).

The importances to this when indexing on the datetime say for instance '2015-06-05 22:47:20.102' the index has to account for every place within the datetime. This becomes very large spatially and bulky. A successful approach that I've leveraged is create a new datetime column and populate the data by rounding the time to the hour and then building the index upon this new column. Example '2015-06-05 22:47:20.102' translates to '2015-06-05 22:00:00.000'. By taking this approach we leave the detailed data alone and can display it or use it by search on this new column which gives us approximately a 10x (at minimum) return on how fast results are returned. This is due to the fact that the index doesn't have to account for the minutes, seconds and millisecond fields.

How to convert index of a pandas dataframe into a column

either:

df['index1'] = df.index

or, .reset_index:

df = df.reset_index(level=0)

---

so, if you have a multi-index frame with 3 levels of index, like:

>>> df
                       val
tick       tag obs        
2016-02-26 C   2    0.0139
2016-02-27 A   2    0.5577
2016-02-28 C   6    0.0303

and you want to convert the 1st (tick) and 3rd (obs) levels in the index into columns, you would do:

>>> df.reset_index(level=['tick', 'obs'])
          tick  obs     val
tag                        
C   2016-02-26    2  0.0139
A   2016-02-27    2  0.5577
C   2016-02-28    6  0.0303

how to convert Index into list?

You can use tolist or list:

print df.index.tolist()

print list(df.index)

But the fastest solution is convert np.arry by values tolist (thanks EdChum)

print df.index.values.tolist()

Sample:

import pandas as pd

idx = pd.Index([u'Newal', u'Saraswati Khera', u'Tohana'])
print idx
Index([u'Newal', u'Saraswati Khera', u'Tohana'], dtype='object')

print idx.tolist()
[u'Newal', u'Saraswati Khera', u'Tohana']

print list(idx)
[u'Newal', u'Saraswati Khera', u'Tohana']

If you need encode UTF-8:

print [x.encode('UTF8') for x in idx.tolist()]
['Newal', 'Saraswati Khera', 'Tohana']

Another solution:

print [str(x) for x in idx.tolist()]
['Newal', 'Saraswati Khera', 'Tohana']

but it would fail if the unicode string characters do not lie in the ascii range.

Timings:

import pandas as pd
import numpy as np

#random dataframe
np.random.seed(1)
df = pd.DataFrame(np.random.randint(10, size=(3,3)))
df.columns = list('ABC')
df.index = [u'Newal', u'Saraswati Khera', u'Tohana']
print df

print df.index
Index([u'Newal', u'Saraswati Khera', u'Tohana'], dtype='object')

print df.index.tolist()
[u'Newal', u'Saraswati Khera', u'Tohana']

print list(df.index)
[u'Newal', u'Saraswati Khera', u'Tohana']

print df.index.values.tolist()
[u'Newal', u'Saraswati Khera', u'Tohana']

In [90]: %timeit list(df.index)
The slowest run took 37.42 times longer than the fastest. This could mean that an intermediate result is being cached 
100000 loops, best of 3: 2.18 µs per loop

In [91]: %timeit df.index.tolist()
The slowest run took 22.33 times longer than the fastest. This could mean that an intermediate result is being cached 
1000000 loops, best of 3: 1.75 µs per loop

In [92]: %timeit df.index.values.tolist()
The slowest run took 62.72 times longer than the fastest. This could mean that an intermediate result is being cached 
1000000 loops, best of 3: 787 ns per loop

How to convert Index to type Int in Swift?

edit/update:

Xcode 11 • Swift 5.1 or later

extension StringProtocol {
    func distance(of element: Element) -> Int? { firstIndex(of: element)?.distance(in: self) }
    func distance<S: StringProtocol>(of string: S) -> Int? { range(of: string)?.lowerBound.distance(in: self) }
}

---

extension Collection {
    func distance(to index: Index) -> Int { distance(from: startIndex, to: index) }
}

---

extension String.Index {
    func distance<S: StringProtocol>(in string: S) -> Int { string.distance(to: self) }
}

---

Playground testing

let letters = "abcdefg"

let char: Character = "c"
if let distance = letters.distance(of: char) {
    print("character \(char) was found at position #\(distance)")   // "character c was found at position #2\n"
} else {
    print("character \(char) was not found")
}

---

let string = "cde"
if let distance = letters.distance(of: string) {
    print("string \(string) was found at position #\(distance)")   // "string cde was found at position #2\n"
} else {
    print("string \(string) was not found")
}

Formatting the index while converting daily data to weekly data, using Pandas

You want unstack(): https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.unstack.html

It will move one of the index levels to be a column in the DataFrame. Something like this:

 df.unstack('Date')

Index was out of range. Must be non-negative and less than the size of the collection parameter name:index

You're not adding columns to your DataGridView

DataGridView dataGridView1 = new DataGridView();//Create new grid

dataGridView1.Columns[0].Name = "ItemID";// refer to column which is not there 

Is it clear now why you get an exception?

Add this line before you use columns to fix the error

dataGridView1.ColumnCount = 5;

---

Related Topics